Conditional percentage denominator based on slicer

Anonymous — Wed, 02 Feb 2022 18:14:13 GMT

Hi there, I am trying to set up a report so that a viewer can select the constraints on a calculation. Specifically, I want them to be able to specify what to include in the denominator of a percentage calculation. Here is a screenshot:

I want the viewer to be able to specify whether the percentages displayed are calculated within the constraints selected in the slicers. Currently, I am calculating the percentages this way to have the denominator reflect the institutions and years displayed:

Denominator =
CALCULATE (
SUM ( CSV_compiled[Degree_Count] ),
ALLEXCEPT ( CSV_compiled, CSV_compiled[inst_name], CSV_compiled[year] )
)

Percent of Programs =
DIVIDE ( CSV_compiled[Sum of Programs], CSV_compiled[Denominator] )

I got as far as creating a table that has the four fields I want viewers to select from (Institution, Degree Type, STEM, Distance Ed) and turning that into a slicer. But I'm not sure how to pull what is selected and incorporate it into my Denominator calculation. Importantly, they need to be able to select all, none, one, or multiple options.

Re: Conditional percentage denominator based on slicer

AlexisOlson — Wed, 02 Feb 2022 22:49:12 GMT

ALLSELECTED should let you read in the slicer filter context.

Denominator = CALCULATE ( SUM ( CSV_compiled[Degree_Count] ), ALLSELECTED ( CSV_compiled ) )

Re: Conditional percentage denominator based on slicer

Anonymous — Thu, 03 Feb 2022 21:35:05 GMT

Thanks, Alexis! Unfortunately this didn't work for me. This adjusted based on the selections within the filters I had set up, but what I wanted was the ability for the viewer to decide which filters it should adjust for vs not.

But! I figured it out. It's not elegant and probably not the most efficient way to do it, but here's what I did...

I created a table with the four slicers and all the possible combined values of including/excluding them. It looks like this:

Then, I just made a verrrrry long calculation for my denominator that considered all the possible combinations:

Denom = IF(CONTAINSROW(Slicers,"Exclude","Exclude","Exclude","Exclude"),

CALCULATE(SUM(CSV_compiled[Degree_Count]),ALLEXCEPT(CSV_compiled,CSV_compiled[year])),

IF(CONTAINSROW(Slicers,"Exclude","Exclude","Exclude","Include"),

CALCULATE(SUM(CSV_compiled[Degree_Count]),ALLEXCEPT(CSV_compiled,'STEM CIP Codes'[STEM],CSV_compiled[year])),

IF(CONTAINSROW(Slicers,"Exclude","Exclude","Include","Exclude"),

CALCULATE(SUM(CSV_compiled[Degree_Count]),ALLEXCEPT(CSV_compiled,CSV_compiled[Distance],CSV_compiled[year])),

IF(CONTAINSROW(Slicers,"Exclude","Exclude","Include","Include"),

CALCULATE(SUM(CSV_compiled[Degree_Count]),ALLEXCEPT(CSV_compiled,CSV_compiled[Distance],'STEM CIP Codes'[STEM],CSV_compiled[year])),

IF(CONTAINSROW(Slicers,"Exclude","Include","Exclude","Exclude"),

CALCULATE(SUM(CSV_compiled[Degree_Count]),ALLEXCEPT(CSV_compiled,CSV_compiled[Degree Type],CSV_compiled[year])),

IF(CONTAINSROW(Slicers,"Exclude","Include","Exclude","Include"),

CALCULATE(SUM(CSV_compiled[Degree_Count]),ALLEXCEPT(CSV_compiled,CSV_compiled[Degree Type],'STEM CIP Codes'[STEM],CSV_compiled[year])),

IF(CONTAINSROW(Slicers,"Exclude","Include","Include","Exclude"),

CALCULATE(SUM(CSV_compiled[Degree_Count]),ALLEXCEPT(CSV_compiled,CSV_compiled[Degree Type],CSV_compiled[Distance],CSV_compiled[year])),

IF(CONTAINSROW(Slicers,"Exclude","Include","Include","Include"),

CALCULATE(SUM(CSV_compiled[Degree_Count]),ALLEXCEPT(CSV_compiled,CSV_compiled[Degree Type],CSV_compiled[Distance],'STEM CIP Codes'[STEM],CSV_compiled[year])),

IF(CONTAINSROW(Slicers,"Include","Exclude","Exclude","Exclude"),

CALCULATE(SUM(CSV_compiled[Degree_Count]),ALLEXCEPT(CSV_compiled,CSV_compiled[inst_name],CSV_compiled[year])),

IF(CONTAINSROW(Slicers,"Include","Exclude","Exclude","Include"),

CALCULATE(SUM(CSV_compiled[Degree_Count]),ALLEXCEPT(CSV_compiled,CSV_compiled[inst_name],'STEM CIP Codes'[STEM],CSV_compiled[year])),

IF(CONTAINSROW(Slicers,"Include","Exclude","Include","Exclude"),

CALCULATE(SUM(CSV_compiled[Degree_Count]),ALLEXCEPT(CSV_compiled,CSV_compiled[inst_name],CSV_compiled[Distance],CSV_compiled[year])),

IF(CONTAINSROW(Slicers,"Include","Exclude","Include","Include"),

CALCULATE(SUM(CSV_compiled[Degree_Count]),ALLEXCEPT(CSV_compiled,CSV_compiled[inst_name],CSV_compiled[Distance],'STEM CIP Codes'[STEM],CSV_compiled[year])),

IF(CONTAINSROW(Slicers,"Include","Include","Exclude","Exclude"),

CALCULATE(SUM(CSV_compiled[Degree_Count]),ALLEXCEPT(CSV_compiled,CSV_compiled[inst_name],CSV_compiled[Degree Type],CSV_compiled[year])),

IF(CONTAINSROW(Slicers,"Include","Include","Exclude","Include"),

CALCULATE(SUM(CSV_compiled[Degree_Count]),ALLEXCEPT(CSV_compiled,CSV_compiled[inst_name],CSV_compiled[Degree Type],'STEM CIP Codes'[STEM],CSV_compiled[year])),

IF(CONTAINSROW(Slicers,"Include","Include","Include","Exclude"),

CALCULATE(SUM(CSV_compiled[Degree_Count]),ALLEXCEPT(CSV_compiled,CSV_compiled[inst_name],CSV_compiled[Degree Type],CSV_compiled[Distance],CSV_compiled[year])),

IF(CONTAINSROW(Slicers,"Include","Include","Include","Include"),

CALCULATE(SUM(CSV_compiled[Degree_Count]),ALLEXCEPT(CSV_compiled,CSV_compiled[inst_name],CSV_compiled[Degree Type],CSV_compiled[Distance],'STEM CIP Codes'[STEM],CSV_compiled[year])),

"Error"

))))))))))))))))

This is probably the maximum number of filters I'd use this approach with, but I managed to make it work. I'd be interested if you or anyone else sees a more efficient way of doing this.

Thank you for contributing!

Re: Conditional percentage denominator based on slicer

Anonymous — Thu, 03 Feb 2022 21:36:21 GMT

Oh, and here's what the final dashboard looks like:

topic Re: Conditional percentage denominator based on slicer in DAX Commands and Tips

Conditional percentage denominator based on slicer

Re: Conditional percentage denominator based on slicer

Re: Conditional percentage denominator based on slicer

Re: Conditional percentage denominator based on slicer