Total of column subtotals with condition in matrix Power BI

[Monster1992]

I need to show value with condition in total column.

If both subtotal columns > 0, then 1 else 0 / blank.
In the subtotal there is 1 if the value 1 is in any of the months.

Visualization

The user selects the value "Apple in menu" & "Orange in menu" from the Measures[Measure] filter. The same values are in table and column  'details'[RED Question].

The measure is selected from an additional table (Measures[Measure]), because the results are based on different tables, not only 'details'.

Menu =

VAR category = SELECTEDVALUE(Measures[Measure])

RETURN
CALCULATE(

                    DISTINCTCOUNT(details[Customer]),

                    'details'[RED Question] = category,

                    details[Answer]>0

)

A fragment of an example data source. If we have 0 in column Answer, I don't want to see it in summary table.

Answer	RED Question	Customer	Month	Quarter
1	Apple in menu	100335	005.2024	2
1	Orange in menu	101496	005.2024	2
0	Apple in menu	100335	006.2024	2

How can I do this?

[SamInogic]

Hi,

Based on our understanding, you want the "Total" column to display a value of 1 if both subtotals (for "Apple in menu" and "Orange in menu") are greater than 0, and 0 or blank otherwise. Here's how you can achieve this:

1. Create a Measure for Each Subtotal:

First, ensure you have separate measures for each category, "Apple in menu" and "Orange in menu". For example:

Measure for "Apple in menu":

 AppleInMenu =CALCULATE(    DISTINCTCOUNT(details[Customer]),    'details'[RED Question] = "Apple in menu",    details[Answer] > 0)

Measure for "Orange in menu":

OrangeInMenu =CALCULATE(    DISTINCTCOUNT(details[Customer]),    'details'[RED Question] = "Orange in menu",    details[Answer] > 0)

2. Create the Conditional Total Measure:

Now, create a new measure for the "Total" column. This measure will check if both "Apple in menu" and "Orange in menu" subtotals are greater than 0, and if so, return 1; otherwise, return 0 or blank.

TotalMenu =IF(    [AppleInMenu] > 0 && [OrangeInMenu] > 0,    1,    0   // Or use BLANK() if you don't want to display 0)

3. Apply the Total Measure to the Table Visual:

In your Power BI table, include:

• [AppleInMenu] in one column,
• [OrangeInMenu] in another column,
• [TotalMenu] in the "Total" column.

This will display 1 if both subtotals are greater than 0, and 0 (or blank) if either subtotal is 0.

4. Filter Out Rows Where Answer = 0:

To ensure rows where details[Answer] = 0 are excluded from the summary table, the measures already use details[Answer] > 0, so any rows with an Answer of 0 will be excluded from the calculation. Additionally, you can apply a report-level filter to hide rows where Answer = 0 in your visual.

5. (Optional) Adjust Display of Zeros to Blanks:

If you prefer to show blanks instead of 0, modify the TotalMenu measure like this:

TotalMenu =IF(    [AppleInMenu] > 0 && [OrangeInMenu] > 0,    1,    BLANK())

This will show a blank cell when the condition isn't met, instead of displaying 0.

Conclusion:

• You now have individual measures for each category ("Apple in menu" and "Orange in menu").
• A conditional total measure that only shows 1 if both subtotals are greater than 0.
• Rows where Answer = 0 are filtered out from the calculation.

This should provide the desired behaviour in your table, with the correct totals based on the specified conditions.

Hope this helps.

Thanks!

[Greg_Deckler]

@Monster1992 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

[Monster1992]

Thank you Greg.

I can't use function HASONEVALUE / HASONEFILTER, because I have subtotals. I don't know how can I separate subtotal and total in code. The subtotal result is correct. Total is empty. I want to base the total result on subtotals. I don't know how to do this.