Calculation of percentages while analyzing survey results with multi-choice questions

[Anonymous]

Hello,

Can anyone support with calculation of percentages while analyzing survey results with multi-choice questions, please?

Data table is structured like below, simplified example (I know this structure is not very good, but restructuring is problematic):

Indicator	Main_Breakdown	Breakdown_Type	Breakdown	Value
Indicator1	Main	Main		10
Indicator1	Breakdown	Sex	Female	6
Indicator1	Breakdown	Sex	Male	4
Indicator1	Breakdown	Fruit preferred	Apple	3
Indicator1	Breakdown	Fruit preferred	Pear	7
Indicator1	Breakdown	Fruit preferred	Banana	4
Indicator1	Breakdown	Fruit preferred	Peach	8
Indicator2	Main	Main		15

As you see, some indicators are disaggregated, some of those disaggregations are multi-choice i.e. sum of categories (fruit preference in the example above) is higher than total (sum of fruit preference is 22 , while total is 10).

How do I write a measure to deal with calculation of percent for such categories?

So that I can have at the end:

Apple	30%
Banana	40%
Pear	70%
Peach	80%

Thanks!

[sturlaws]

Hi @Anonymous ,

could you try something like this?

Measure =
DIVIDE (
    CALCULATE (
        SUM ( 'Table'[Value] );
        FILTER (
            ALLEXCEPT ( 'Table'; 'Table'[Breakdown]; 'Table'[Indicator] );
            'Table'[Breakdown_Type] = "Fruit preferred"
        )
    );
    CALCULATE (
        SUM ( 'Table'[Value] );
        FILTER (
            ALLEXCEPT ( 'Table'; 'Table'[Indicator] );
            'Table'[Breakdown_Type] = "Main"
        )
    )
)

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.

[Anonymous]

Thanks, @sturlaws , but not exactly.
In your solution the denominator is 25 (Indicator1 + Indicator2), while I need the denominator to be exactly from those indicator(s) where this vreakdown (disaggregation) is applicable.
So in this case the denominator has to be 10, not 25.

[sturlaws]

Since you did not specify how to handle the different indicators, and I wanted to provide you with a generic solution, yes, it sums up the number participants for each indicator.

Add Indicator to a slicer and select accordingly, or add Indicator to your visual to split the calculation per indicator.

You can off course add Indicator="Indicator 1" to the filter part of the measures, but if you have 50 indicators, you would have to create 50 measures with this approach. Doable, but not how Power BI is intended to work.

[Anonymous]

Thanks, well noted.

So there is no obvious way to make a generic formula without adding slicer or hardcoding?

I mean to create a generic formula "put into denominator all Main Values of those indicators where this breakdown occurs".

[sturlaws]

ah, sorry, miunderstood that.

Is it safe to assume that each indicator will have breakdown_type=sex, and that the sum of the rows where breakdown_type=sex is equal to breakdown_type=main?

in that case you can change the code to this:

Measure = 
DIVIDE (
    CALCULATE (
        SUM ( 'Table'[Value] );
        FILTER (
            ALLEXCEPT ( 'Table'; 'Table'[Breakdown]; 'Table'[Indicator] );
            'Table'[Breakdown_Type] = "Fruit preferred"
        )
    );
    CALCULATE (
        SUM ( 'Table'[Value] );
        FILTER (
            ALLEXCEPT ( 'Table'; 'Table'[Indicator] );
            'Table'[Breakdown_Type] = "Sex"
        )
    )
)

[Anonymous]

Thanks for this, but not really.

The original data table is more complex, sometimes there is one disaggregation, but not another..
So it's hardly possible to create a generic formula like "put into denominator all Main Values of those indicators where this breakdown occurs"?

[sturlaws]

no, it is still possible, just looking for the easy way out.

How can the breakdown be identified? Main_breakdown=breakdown?

[Anonymous]

Yes, indeed, Main_breakdown=breakdown. Then type of breakdown in Breakdown_Type.

[sturlaws]

Measure =
VAR _breakdowns =
    CALCULATETABLE (
        VALUES ( 'Table'[Indicator] );
        FILTER ( 'Table'; 'Table'[Main_Breakdown] = "Breakdown" )
    )
RETURN
    DIVIDE (
        CALCULATE (
            SUM ( 'Table'[Value] );
            FILTER (
                ALLEXCEPT ( 'Table'; 'Table'[Breakdown]; 'Table'[Indicator] );
                'Table'[Breakdown_Type] = "Fruit preferred"
            )
        );
        CALCULATE (
            SUM ( 'Table'[Value] );
            FILTER (
                'Table';
                'Table'[Main_Breakdown] = "Main"
                    && 'Table'[Indicator] IN _breakdowns
            )
        )
    )

[Anonymous]

Sorry, maybe I am doing something wrong, but doesn't work.

Can you check the PBIX, please?

https://drive.google.com/file/d/1WqfkEQvDVTxt-QAJvnyAk0RkhnD2DlCC/view?usp=sharing

[sturlaws · Accepted solution]

The code was missing an ALL():

Measure = 
VAR _breakdowns =
    CALCULATETABLE (
        VALUES ( 'Table'[Indicator] );
        FILTER ( 'Table'; 'Table'[Main_Breakdown] = "Breakdown" )
    )
RETURN
    DIVIDE (
        CALCULATE (
            SUM ( 'Table'[Value] );
            FILTER (
                ALLEXCEPT ( 'Table'; 'Table'[Breakdown]; 'Table'[Indicator] );
                'Table'[Breakdown_Type] = "Fruit preferred"
            )
        );
        CALCULATE (
            SUM ( 'Table'[Value] );
            FILTER (
                all('Table');
                'Table'[Main_Breakdown] = "Main"
                    && 'Table'[Indicator] IN _breakdowns
            )
        )
    )

[Anonymous]

Thank you so much @sturlaws , looks great!