Help with proportions and select all questions

[PowerMoon]

Hi

I am trying to report the proportions of participants who selected each value (diverts attention, staff hours, etc.) among all everyone who selected a particular attribute. Survey participants were instructed to ‘select all’ that apply, so the proportions do not add up to 100%.

The denominator is different for each attribute depending on an answer to a previous question. Data come from an unpivoted table.

Thanks in advance!

[PowerMoon]

Thanks, Gao. The denominator is all who selected an attribute in a previous question rather than the whole table. Is that possible?

[Anonymous]

Hi @PowerMoon ,

Understand, it seems unlikely.😔 These calculations are based on the current context, and once the slicer changes, the context changes as well.

Best Regards,
Gao
Community Support Team

[PowerMoon]

OK thanks 🙂

[PowerMoon]

Thanks, here's a screenshot of the data

(sorry if I misunderstood your question)

[Anonymous]

Hi @PowerMoon ,

Please try this measure:

Proportion =
VAR _each =
    COUNTROWS ( 'Table' )
VAR _total =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        ALLSELECTED ( 'Table'[Attribute.1] ),
        ALL ( 'Table'[Value] )
    )
VAR _rate =
    DIVIDE ( _each, _total )
RETURN
    _rate

Best Regards,
Gao
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!

How to get your questions answered quickly -- How to provide sample data

[PowerMoon]

Thanks, Gao

I think this is on the right track, but the proportions should not add up to 100% as people could 'select all that apply'.  Thanks for replying!

[Anonymous · Accepted solution]

Hi @PowerMoon ,

Please try:

Proportion = 
VAR _each =
    COUNTROWS ( 'Table' )
VAR _total =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        ALL ( 'Table'[Attribute.1] ),
        ALL ( 'Table'[Value] )
    )
VAR _rate =
    DIVIDE ( _each, _total )
RETURN
    _rate

Best Regards,
Gao
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!

How to get your questions answered quickly -- How to provide sample data

[amitchandak]

@PowerMoon ,
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.