cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper V
Helper V

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
Super User IV
Super User IV

@sabd , 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])



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

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?

Community Support
Community Support

Hi @sabd ,

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
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors