Dynamics F/SCM Customer Experience (CRM) Dynamics GP Dynamics Business Central (NAV) Dynamics SL Power Platform Fabric Copilot

[Sam Duval]

Hi All,
 

Trying to calculate the percent of total so that when I filter the table to show just one month, the totals recalc up to 100% . Current if I filter the table to be just April, my %Total Balance stays at 63%

 

This is the code I tried”

DateFilterStart = EOMONTH(SELECTEDVALUE(StartDates[Date].[Date],MIN(Skip_Deferred_Payments_Reports[MostRecentExtensionProcessingDate])),-1)+1

DateFilterEnd = max(Skip_Deferred_Payments_Reports[MostRecentExtensionProcessingDate])

DateDilfterDateDiff = DATEDIFF([DateFilterStart],[DateFilterEnd],MONTH)

 

 

Percent_Of_Total_Balance_Test = var __ExtensionStatus =[ActiveFilter]

var __StartDate =[DateFilterStart]

var __EndDate =[DateFilterEnd]

var __DateDiff=DATEDIFF(__StartDate,__EndDate,MONTH)

var filteredtable=

FILTER(all(Skip_Deferred_Payments_Reports),and(Skip_Deferred_Payments_Reports[ActiveOrExpired]=__ExtensionStatus,and(Skip_Deferred_Payments_Reports[MostRecentExtensionProcessingDate]>=__StartDate,Skip_Deferred_Payments_Reports[MostRecentExtensionProcessingDate]<=__EndDate)))

var filteredtabledateonly=

FILTER(all(Skip_Deferred_Payments_Reports),and(Skip_Deferred_Payments_Reports[MostRecentExtensionProcessingDate]>=__StartDate,Skip_Deferred_Payments_Reports[MostRecentExtensionProcessingDate]<=__EndDate))

return

SWITCH(TRUE(),

and([ActiveFilter]=0,[DateDilfterDateDiff]=0),

        CALCULATE(DIVIDE([Total_Balance],SUMX(((filteredtabledateonly)),Skip_Deferred_Payments_Reports[BALANCE])))

,[ActiveFilter]<>0,

        CALCULATE(DIVIDE([Total_Balance],SUMX((filteredtable),Skip_Deferred_Payments_Reports[BALANCE])))

,[ActiveFilter]=0,

        CALCULATE(DIVIDE([Total_Balance],SUMX((all(Skip_Deferred_Payments_Reports)),Skip_Deferred_Payments_Reports[BALANCE])))

       

)

 

 

It works, but it isolates the totals to each month, so march totals up to 100% and April Totals to 100%. When showing 2 or more months, I want each month to be the percent of the displayed total. Any ideas how to do this better or what I’m missing?

——————————
Sam Duval
Data Quality Analyst
Element Financial
Indianapolis IN
——————————

[Sam Duval]

Ok so I may have made a mountain out of a ant hill, when clearly the built in show value as % should do what I wanted. But if anyone wants to take a crack at cleaning up my DAX or solving the original question via DAX i’m always looking to expand my DAX code library

——————————
Sam Duval
Data Quality Analyst
Element Financial
Indianapolis IN
——————————
——————————————-

[William Skelley]

Hi Sam:

I think you will want to use the ALLSELECTED command, so you can get share based on your filtering. I have included an example. I hope this helps.

Best regards,

Bill S

——————————
William Skelley
01106
Longmeadow MA
8602807221
——————————
——————————————-

[Sam Duval]

Looks like that did the trick thanks. ?

an idea how to take it a level deeper and get the sub totals? 
This is what I have today:

% Sub Total Balance = divide([Total_Balance],LOOKUPVALUE(Sub_Totals[Balance],Sub_Totals[Subtotal_Key],SELECTEDVALUE(Skip_Deferred_Payments_Reports[Subtotal_Key])))

——————————
Sam Duval
Data Quality Analyst
Element Financial
Indianapolis IN
——————————
——————————————-

[William Skelley]

Hi Sam:

Have you tried putting the info into a matrix visual? That has sub-total button in formatting. Otherwise you could be going down the road of have a template which is a bigger topic. There are YouTubes on income statements in PBI and that helped me.(with templates and sub-totals)

Thanks,

Bill

——————————
William Skelley
01106
Longmeadow MA
8602807221
——————————
——————————————-

[Sam Duval]

It is in a matrix already, so i must be missing something with the sub-total you referred to, I couldn’t find a way to get indirect autos to show as a percentage of the deferment total vs the overall total… I got a solution, i think i could have done it with table variables but couldn’t figure out how to reference the table variable columns correctly, so i gave up and created 3 calculated tables instead. and just added a switch to control the lookup statment

% Sub Total Balance = SWITCH(TRUE(),

[ActiveFilter]=0

,divide([Total_Balance],LOOKUPVALUE(Sub_Totals_Combined[Balance],Sub_Totals_Combined[Subtotal_Key],SELECTEDVALUE(Skip_Deferred_Payments_Reports[Subtotal_Key])))

,[ActiveFilter]=”Active”

,divide([Total_Balance],LOOKUPVALUE(Sub_Totals_Active_Only[Balance],Sub_Totals_Active_Only[Subtotal_Key],SELECTEDVALUE(Skip_Deferred_Payments_Reports[Subtotal_Key])))

,[ActiveFilter]=”Expired”

,divide([Total_Balance],LOOKUPVALUE(Sub_Totals_Expired_Only[Balance],Sub_Totals_Expired_Only[Subtotal_Key],SELECTEDVALUE(Skip_Deferred_Payments_Reports[Subtotal_Key])))

)

——————————
Sam Duval
Data Quality Analyst
Element Financial
Indianapolis IN
——————————
——————————————-