Matrix total not showing

[Anonymous]

Hi,

I have below DAX, it does not show the total, I would like to show the total regardless of the visual, I will be using this measure in calculation of other measures:

Budget2 Volume Target =

VAR CapVic = CALCULATE

(CALCULATE(

SUM('KPI Target'[Value]),

FILTER( 'KPI Target','KPI Target'[Target Code] ="BUDGET VOL"

&& 'KPI Target'[Dimension 2 Value] = ">CAP"),

ALLEXCEPT('Posting Date', 'Posting Date'[Date])

), CROSSFILTER('KPI Target'[LocationLookupKey], Location[LocationLookupKey], None)

)

VAR BugdetVolAll = CALCULATE(

SUM('KPI Target'[Value]),

FILTER( 'KPI Target','KPI Target'[Target Code] ="BUDGET VOL"

&& 'KPI Target'[Open] =1))

Return

IF(HASONEVALUE(Location[County]),

IF(VALUES(Location[County]) = "VIC",

CapVic + BugdetVolAll,

BugdetVolAll

)

)

Not sure why, can someone help?

Thanks.

[amitchandak]

@Anonymous , because you have used hasonevalue and grand total does not have one

you can try a new measure like. or add this return value

Sumx(Values(Location[County]) , [Budget2 Volume Target])

[Anonymous]

Thanks, it worked.

I tired to do it all in one measure, but did not work, like below:

Budget Volume Target =
VAR CapVic = CALCULATE
(CALCULATE(
SUM('KPI Target'[Value]),
FILTER( 'KPI Target','KPI Target'[Target Code] ="BUDGET VOL"
&& 'KPI Target'[Dimension 2 Value] = ">CAP"),
ALLEXCEPT('Posting Date', 'Posting Date'[Date])
),
CROSSFILTER('KPI Target'[LocationLookupKey], Location[LocationLookupKey], None)
)
VAR BugdetVolAll = CALCULATE(
SUM('KPI Target'[Value]),
FILTER( 'KPI Target','KPI Target'[Target Code] ="BUDGET VOL"
&& 'KPI Target'[Open] =1))
VAR AllTarget=

IF(HASONEVALUE(Location[County]),
IF(VALUES(Location[County]) = "VIC",
CapVic + CALCULATE(BugdetVolAll, FILTER( 'KPI Target','KPI Target'[Open] =1)),
CALCULATE(BugdetVolAll, FILTER( 'KPI Target','KPI Target'[Open] =1))
)
)
Return
SUMX(VALUES(Location[County]), AllTarget)

Do you kno why?

[v-yiruan-msft]

Hi @Anonymous ,

Please view the video in the following link, you may get the answer from there.

Why Your Total Is Incorrect In Power BI - The Key DAX Concept To Understand

Best regards

Rena