topic Re: Matrix with Measure doesn't SUM in DAX Commands and Tips

Matrix with Measure doesn't SUM

ROOY1 — Thu, 11 Apr 2024 14:14:17 GMT

Hello all,

I made a Measure but i am not receiving the expected results on my martix visual in PBI.

I want for every staff member who is ZZP or Charter and drove 1 or more orders on a day a 1 and not a count since we are intressted in FTE and not the amout of orders.

Aantal extern test =

VAR CurrentDate = SELECTEDVALUE(RITDETAILS[DATUM])

VAR CurrentStaffNum = SELECTEDVALUE(Ritdetails[StaffNumber])

RETURN

IF(CALCULATE(COUNTROWS(Ritdetails), FILTER(Ritdetails, Ritdetails[Transporteur] = "ZZP" || Ritdetails[Transporteur] = "Charter" && Ritdetails[StaffNumber] = CurrentStaffNum && Ritdetails[Datum] = CurrentDate)) >=1, 1, BLANK())

Here you can see that the total of 01-04-2024 is showing 1 but I expect here the sum so 4.

I hope that someone can help me with this situation.

Cheers Roy

Re: Matrix with Measure doesn't SUM

Greg_Deckler — Thu, 11 Apr 2024 14:32:45 GMT

ROOY1 First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, 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/547907

Also: https://youtu.be/uXRriTN0cfY
And: https://youtu.be/n4TYhF2ARe8

Re: Matrix with Measure doesn't SUM

Anonymous — Fri, 12 Apr 2024 05:34:35 GMT

Hi ROOY1 ,

Thanks for the reply from Greg_Deckler , please allow me to provide another insight:

Please modify your measure syntax:

Aantal Extern Test Revised = SUMX( VALUES(RITDETAILS[DATUM]), VAR CurrentDate = RITDETAILS[DATUM] VAR CurrentStaffNum = SELECTEDVALUE(Ritdetails[StaffNumber]) RETURN IF( CALCULATE( COUNTROWS(Ritdetails), FILTER( Ritdetails, Ritdetails[Transporteur] IN {"ZZP", "Charter"} && Ritdetails[StaffNumber] = CurrentStaffNum && Ritdetails[Datum] = CurrentDate ) ) >= 1, 1, BLANK() ) )

If it does not work after modification, please provide me with sample data, please remove any sensitive data in advance.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

Re: Matrix with Measure doesn't SUM

ROOY1 — Wed, 17 Apr 2024 12:17:20 GMT

Hi Yang,

I have tried your modification and here is the result:

Now column subtotals are working but row subtotals are not working yet :(.

Re: Matrix with Measure doesn't SUM

Anonymous — Thu, 18 Apr 2024 02:32:37 GMT

Hi ROOY1 ,

I created the sample data myself but both row and column subtotals work fine, unfortunately I can not reproduce your problem.

I can not help you very well. Maybe you can provide me with your pbix file?

Please remove sensitive data and do not log in to your account in Power BI Desktop when uploading pbix files.

All this is to better solve your problems.

I would be grateful if you get back to me quickly.

Best Regards,
Yang
Community Support Team