Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Matrix total not showing

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.
3 REPLIES 3
amitchandak
Super User
Super User

@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
Not applicable

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?

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

Community Support Team _ Rena Ruan
If this post helps, then please consider Accept it as the solution to help the other members find it more.
Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.