topic Re: Sum based on specific grouping condition in DAX Commands and Tips

Sum based on specific grouping condition

bmk — Thu, 14 Jul 2022 21:50:50 GMT

Hello,

Based on table below, I wish to create two measures 'total_metric1' and 'total_metric2'. However, the correct total as per my use case for total_metric1 = 500 and total_metric2 = 1700. At the moment, PBI is totaling it as 1000 and 3400 respectively.

How can I achieve this optimally via DAX?

Re: Sum based on specific grouping condition

WinterMist — Fri, 15 Jul 2022 02:01:17 GMT

bmk

Hopefully this is helpful to you.

Regards,

Nathan

Re: Sum based on specific grouping condition

WinterMist — Fri, 15 Jul 2022 02:28:33 GMT

bmk

So if you add 01B or 02B to a card visual, they work.

However, if you try to add them to the table visual, you get the following error:

"SummarizeColumns() and AddMissingItems() may not be used in this context."

That's annoying.

The following link provides guidance that we should replace SUMMARIZECOLUMNS with ADDCOLUMNS+SUMMARIZE.

https://community.powerbi.com/t5/Desktop/Issue-with-Bar-chart-SummarizeColumns-and-AddMissingItems-may/m-p/1145554#M519845

So I have modified the code here in 01C & 02C.

As a result, these now work correctly within the table visual, as shown.

Regards,

Nathan

Re: Sum based on specific grouping condition

bmk — Fri, 15 Jul 2022 03:23:36 GMT

Thank you!

Re: Sum based on specific grouping condition

WinterMist — Fri, 15 Jul 2022 03:27:35 GMT

bmk

No problem. Glad to help!