Total not showing in table views for average measure

Anonymous — Thu, 01 Sep 2022 12:58:26 GMT

Hello,

I'm at my wits end with this issue. I'm struggling to make it so the total of the table is printed. I believe the issue is with the context of the total but I'm not sure how to solve it.

I have the following table:

Here is the breakdown of the columns:

Date comes from a Calendar

Meas_FTDS is a measure calculated this way:

CALCULATE(

SUMX(Fact_Earnings,Fact_Earnings[ftds]),

filter(Fact_Earnings,Fact_Earnings[Incomplete extract]=0))

Just a sumx of the ftds column and filtered so it does not return any incomplete extract.

FC_FTD 13M MA:

VAR

FTD1mAgo = CALCULATE([Meas_FTDS],dateadd(_Calendar[Date], -1, MONTH ) )

VAR

FTD2mAgo = CALCULATE([Meas_FTDS],dateadd(_Calendar[Date], -2, MONTH ) )

VAR

FTD3mAgo = CALCULATE([Meas_FTDS],dateadd(_Calendar[Date], -3, MONTH ) )

VAR

FTD4mAgo = CALCULATE([Meas_FTDS],dateadd(_Calendar[Date], -4, MONTH ) )

VAR

FTD5mAgo = CALCULATE([Meas_FTDS],dateadd(_Calendar[Date], -5, MONTH ) )

VAR

FTD6mAgo = CALCULATE([Meas_FTDS],dateadd(_Calendar[Date], -6, MONTH ) )

VAR

FTD7mAgo = CALCULATE([Meas_FTDS],dateadd(_Calendar[Date], -7, MONTH ) )

VAR

FTD8mAgo = CALCULATE([Meas_FTDS],dateadd(_Calendar[Date], -8, MONTH ) )

VAR

FTD9mAgo = CALCULATE([Meas_FTDS],dateadd(_Calendar[Date], -9, MONTH ) )

VAR

FTD10mAgo = CALCULATE([Meas_FTDS],dateadd(_Calendar[Date], -10, MONTH ) )

VAR

FTD11mAgo = CALCULATE([Meas_FTDS],dateadd(_Calendar[Date], -11, MONTH ) )

VAR

FTD12mAgo = CALCULATE([Meas_FTDS],dateadd(_Calendar[Date], -12, MONTH ) )

VAR

FTD13mAgo = CALCULATE([Meas_FTDS],dateadd(_Calendar[Date], -13, MONTH ) )

VAR

MA = CALCULATE(DIVIDE( FTD1mAgo + FTD2mAgo + FTD3mAgo + FTD4mAgo + FTD5mAgo + FTD6mAgo + FTD7mAgo + FTD8mAgo + FTD9mAgo + FTD10mAgo + FTD11mAgo + FTD12mAgo+ FTD13mAgo, 13,0))

VAR

OnlyFC = IF(ISBLANK([Meas_FTDS]),

CALCULATE(MA, SAMEPERIODLASTYEAR(_Calendar[Date])),

BLANK())

RETURN

OnlyFC

When I Return the variable MA I get the total amount but I want to filter so this column only returns values at the total or on days I don't have a figure in column Meas_FTDS That way I can get both in a single measure later.

I would greatly appreciate any help.

Re: Total not showing in table views for average measure

tamerj1 — Thu, 01 Sep 2022 15:04:27 GMT

Anonymous

Do you want to return the average of the visible values or the sum of the visible values?

Re: Total not showing in table views for average measure

Anonymous — Thu, 01 Sep 2022 15:24:42 GMT

Hello, the sum of visible values.

Re: Total not showing in table views for average measure

tamerj1 — Thu, 01 Sep 2022 15:38:26 GMT

Anonymous

You can use a new measure

FC_FTD 13M MA 2 =
SUMX ( VALUES ( _Calendar[Date] ), [FC_FTD 13M MA] )

Re: Total not showing in table views for average measure

Anonymous — Thu, 01 Sep 2022 16:51:28 GMT

Thanks a lot! I guess the new measure gets a different context than any carried over from the variables?

Re: Total not showing in table views for average measure

tamerj1 — Thu, 01 Sep 2022 17:17:01 GMT

Anonymous
Exactly

topic Total not showing in table views for average measure in DAX Commands and Tips

Total not showing in table views for average measure

Re: Total not showing in table views for average measure

Re: Total not showing in table views for average measure

Re: Total not showing in table views for average measure

Re: Total not showing in table views for average measure

Re: Total not showing in table views for average measure