Sum based on specific grouping condition

[bmk]

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?

[WinterMist]

@bmk 

No problem.  Glad to help!

[WinterMist · Accepted solution]

@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-m...

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

[bmk]

Thank you!

[WinterMist]

@bmk 

Hopefully this is helpful to you.

Regards,

Nathan