Forum Discussion
Total in table is incorrect vs individual rows
The Total at the bottom of my table is not showing correctly even tho the individual rows are and I don't know how to fix it.
I've tried this article that Greg_Deckler posted but I am a little lost because I have a switch statement in my measure so its not so black and white.
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376
Column = My database index
Calculated Column = Is a calculated column I created that creates a rolling inventory within the database
Est. EOD =
VAR _min =
CALCULATE (
MIN ( shipment[Column] ),
FILTER ( ALLSELECTED ( shipment ), shipment[Calculated Column] < 0 )
)
VAR __min = IF ( SUM ( shipment[Calculated Column] ) >= 0, BLANK (), _min )
VAR _index = CALCULATE(SUM(shipment[Column]))
//RETURN IF(__min = _index, SUM(shipment[Remaining]) + SUM(shipment[Calculated Column]), "Go")
VAR _final = SWITCH(TRUE(),
__min = BLANK(), 0,
__min = _index, -1 * CALCULATE(SUM(shipment[Calculated Column])),
__min < _index, CALCULATE(SUM(shipment[Remaining])))
VAR __table = SUMMARIZE(shipment,shipment[location],"__value",_final)
RETURN IF(HASONEVALUE(shipment[location]),_final,SUMX(__table,[__value]))
Any help would be appreciated!
Hi thmonte ,
You may try to create two measures instead of a single measure based on your original formula , like DAX below.
Est. EOD = VAR _min = CALCULATE ( MIN ( shipment[Column] ), FILTER ( ALLSELECTED ( shipment ), shipment[Calculated Column] < 0 ) ) VAR __min = IF ( SUM ( shipment[Calculated Column] ) >= 0, BLANK (), _min ) VAR _index = CALCULATE(SUM(shipment[Column])) //RETURN IF(__min = _index, SUM(shipment[Remaining]) + SUM(shipment[Calculated Column]), "Go") Return SWITCH(TRUE(), __min = BLANK(), 0, __min = _index, -1 * CALCULATE(SUM(shipment[Calculated Column])), __min < _index, CALCULATE(SUM(shipment[Remaining]))) //the following DAX will return correct total . Est. EOD _New= VAR __table = SUMMARIZE(shipment,shipment[location],"__value", [Est. EOD]) RETURN IF(HASONEVALUE(shipment[location]),[Est. EOD],SUMX(__table,[__value]))Best Regards,
Amy
Community Support Team _ Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
thmonte - Have you seen this article?
Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907In looking at your code, you need to move your calculation of _final into your SUMMARIZE statement.
Hi thmonte ,
You may try to create two measures instead of a single measure based on your original formula , like DAX below.
Est. EOD = VAR _min = CALCULATE ( MIN ( shipment[Column] ), FILTER ( ALLSELECTED ( shipment ), shipment[Calculated Column] < 0 ) ) VAR __min = IF ( SUM ( shipment[Calculated Column] ) >= 0, BLANK (), _min ) VAR _index = CALCULATE(SUM(shipment[Column])) //RETURN IF(__min = _index, SUM(shipment[Remaining]) + SUM(shipment[Calculated Column]), "Go") Return SWITCH(TRUE(), __min = BLANK(), 0, __min = _index, -1 * CALCULATE(SUM(shipment[Calculated Column])), __min < _index, CALCULATE(SUM(shipment[Remaining]))) //the following DAX will return correct total . Est. EOD _New= VAR __table = SUMMARIZE(shipment,shipment[location],"__value", [Est. EOD]) RETURN IF(HASONEVALUE(shipment[location]),[Est. EOD],SUMX(__table,[__value]))Best Regards,
Amy
Community Support Team _ Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
