cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
MDMC Frequent Visitor
Frequent Visitor

Incorrect Total from Measure Incorrect

Hi, I'm new to DAX and PowerBI.  I have a fairly simple measure but cannot seem to get it to work.  Any help would be appreciated!

I am working in a live data model so I am limited to creating measures.  I have the following measure:

Active =
VAR TotalSpare = CALCULATE(SUM('table'[QuantityValue]), 'table'[Type] = 1 ||'table'[Type] = 3 
RETURN
IF([Recommended]<= TotalSpare, [Recommended], TotalSpare)
 
"Recommended" is another measure.  I cannot get the correct total, even though the line totals are correct.  At one point I added SUMX function prior to the IF statement, but got the same result.  
 2020-01-20_21-19-38.png
1 ACCEPTED SOLUTION

Accepted Solutions
JustJan Member
Member

Re: Incorrect Total from Measure Incorrect

Hi @MDMC ,

 

The grand totals line is not calculated from the table above, but is recalculated at the grand total line. So the filter conditions for the measure calculation are different in that case. 

 

Try something like this: 

 

=IF(HASONEVALUE(Table[Category])
,[Active]
,SUMX(SUMMARIZE(VALUES(Table[Category]),Table[Category],"NumberOfActives",[Active]),[NumberOfActives])
)

 

basically it tells the measure to use your [Active] measure when one line level and when on the grand total line Summarize the values of [Active]. I referred to the first blurred column as Table[Category].

 

Hope this helps.

 

Jan 

if this is a solution for you, don't forget to mark it as such. thanks

 

View solution in original post

2 REPLIES 2
JustJan Member
Member

Re: Incorrect Total from Measure Incorrect

Hi @MDMC ,

 

The grand totals line is not calculated from the table above, but is recalculated at the grand total line. So the filter conditions for the measure calculation are different in that case. 

 

Try something like this: 

 

=IF(HASONEVALUE(Table[Category])
,[Active]
,SUMX(SUMMARIZE(VALUES(Table[Category]),Table[Category],"NumberOfActives",[Active]),[NumberOfActives])
)

 

basically it tells the measure to use your [Active] measure when one line level and when on the grand total line Summarize the values of [Active]. I referred to the first blurred column as Table[Category].

 

Hope this helps.

 

Jan 

if this is a solution for you, don't forget to mark it as such. thanks

 

View solution in original post

MDMC Frequent Visitor
Frequent Visitor

Re: Incorrect Total from Measure Incorrect

That worked!  Thank you so much!

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors