Note - this is a power pivot question. Can't get percentage of cases to work correctly

[BaldAccountant]

PLease note this is a power pivot question, not power BI
I use a dynamic formula in power pivot where the user can choose the calculation they want to see.  I use the switch function for that.

One of the calculations I want to use is the precentage of cases.  For example we might be looking at cases by specialty and Specialty A has 25% of the cases and Specialty B had 13%, etc.

This is the formula I am using: 

Percent of Cases:=format(divide([Cases],CALCULATE([Cases],ALLSELECTED(Data2)),0),"#0%;(#0%);-")

This formula works fine in power BI, but not power pivot.  I have tried this also

Percent of Cases:=format(divide([Cases],CALCULATE([Cases],ALLSELECTED(Data2),all(Specialties)),0),"#0%;(#0%);-")

But all of the Specialties come out 100%

[v-karpurapud]

Hi @BaldAccountant 

We would like to confirm whether the issue has been resolved. If it is still outstanding, please share any additional information so we can assist you further.

Thank you.

[v-karpurapud]

Hi @BaldAccountant 

I would also take a moment to thank @MFelix   , for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

We would like to confirm whether the issue has been resolved. If it is still outstanding, please share any additional information so we can assist you further.

Best Regards, 
Community Support Team  

[BaldAccountant · Accepted solution]

Here is a solution that avoids having to enter filter1 and filter2 on each line, but it gives a 0 for the specialties I am trying to filter out using filter2.

Analysis Measure:=Var Filter1 = FILTER(Data2,

CONTAINS(VALUES(Groups2[Group Value]),Groups2[Group Value],Data2[FC])||

CONTAINS(VALUES(Groups2[Group Value]),Groups2[Group Value],Data2[Specialty])||

CONTAINS(VALUES(Groups2[Group Value]),Groups2[Group Value],Data2[Age Bucket])||

CONTAINS(VALUES(Groups2[Group Value]),Groups2[Group Value],Data2[Status])||

CONTAINS(VALUES(Groups2[Group Value]),Groups2[Group Value],Data2[Doctor]))

Var Filter2 = filter(Data2,Data2[Include]="Include")

Return

CALCULATE(Switch([Selected Analysis],

"Average Age",[Average Age],

"Average Charges per Case",[Average Charges per Case],

"Average Contribution Margin per Case",[Contribution Margin per Case],

"Average Cost per Case",[Average Cost per Case],

"Average Net Profit per Case",[Average Net Profit per Case],

"Average Reimbursement per Case",[Average Reimbursement per Case],

"Avg Cost W/O Max",[Avg Cost W/O Max],

"Cases",[Cases],

"Charges",sum(Data2[CHARGES]),

"CM per OR Hour",[CM per OR Hour],

"Cont Margin",[Contribution Margin],

"Exp % Reimb",format([Exp % Reimb],"#0%;(#0%);-"),

"Fixed Cost",sum(Data2[FIXED]),

"Fixed Cost per Case",[Fixed Cost per Case],

"Fixed Cost per OR Hour",[Fixed Cost per OR Hour],

"Implant % of Cases",format([Implant % of Cases],"#0%;(#0%);-"),

"Implant Cases",sum(Data2[Implant Case]),

"Implant Cost",sum(Data2[Implant Cost]),

"Implant Cost per Case",[Implant Cost per Case],

"Max Cost",max(Data2[VARIABLE]),

"Max Implant Cost",max(Data2[Implant Cost]),

"Min Cost",MIN(Data2[VARIABLE]),

"Min Implant Cost",min(Data2[Implant Cost]),

"MCR % Cases",FORMAT([MCR % Cases],"#0%;(#0%);-"),

"MCR % Charges",FORMAT([MCR % Charges],"#0%;(#0%);-"),

"MCR % Variable Cost",[MCR % Variable Cost],

"Minutes per Case",[Minutes per Case],

"Net Profit",[Net Profit],

"Non Implant Cases",sum(Data2[Non Implant Case]),

"NP per OR Hour",[NP per OR Hour],

"OR Minutes",sum('Time'[VARIANCE]),

"Percent of Cases",format(Divide([Cases],CALCULATE([Cases],ALL(Data2),Filter2)),"#0.00%;(#0.00%);-"),

"Reimbursement",[Estimated Reimbursement],

"Roll Avg Cost",[Roll Avg Cost],0),Filter1,Filter2)

[MFelix]

Hi @BaldAccountant ,

Not sure what you want to achieve with this but it looks to be a very complex formula just to make a change between values, but again this can happen if you have a very specific model so without context is difficult to give you a better alternative.

Glad you were able to figure it out.

Don't forget to accept the correct answer, even if it is yours, to help other users.

[MFelix]

Hi @BaldAccountant ,

Based on the calculation you present I assume that you have two tables on for data and the other one for Specialties, also based on the result you say you are having I believe that you are using the Specialty from the Data table instead of the Specialty this will return always 100% because when you apply the ALL to the sepecialties there is no context to return the correct calculation for each line.

Also based on this you can have your measure to simply be:

Percent of Cases:= divide([Cases],CALCULATE([Cases],ALLSELECTED(Specialties[Specialty])),0)

The format can be done  in the formatting pane:

[BaldAccountant]

Thanks again for your answer, but I came up with a solution that works.  

Here is my dynamic formula:

Analysis Measure:=Var Filter1 = FILTER(Data2,

CONTAINS(VALUES(Groups2[Group Value]),Groups2[Group Value],Data2[FC])||

CONTAINS(VALUES(Groups2[Group Value]),Groups2[Group Value],Data2[Specialty])||

CONTAINS(VALUES(Groups2[Group Value]),Groups2[Group Value],Data2[Age Bucket])||

CONTAINS(VALUES(Groups2[Group Value]),Groups2[Group Value],Data2[Status])||

CONTAINS(VALUES(Groups2[Group Value]),Groups2[Group Value],Data2[Doctor]))

Var Filtert2 = filter(Data2,Data2[Include]="Include")

Return

CALCULATE(Switch([Selected Analysis],

"Average Age",[Average Age],

"Average Charges per Case",[Average Charges per Case],

"Average Contribution Margin per Case",[Contribution Margin per Case],

"Average Cost per Case",[Average Cost per Case],

"Average Net Profit per Case",[Average Net Profit per Case],

"Average Reimbursement per Case",[Average Reimbursement per Case],

"Avg Cost W/O Max",[Avg Cost W/O Max],

"Cases",[Cases],

"Charges",sum(Data2[CHARGES]),

"CM per OR Hour",[CM per OR Hour],

"Cont Margin",[Contribution Margin],

"Exp % Reimb",format([Exp % Reimb],"#0%;(#0%);-"),

"Fixed Cost",sum(Data2[FIXED]),

"Fixed Cost per Case",[Fixed Cost per Case],

"Fixed Cost per OR Hour",[Fixed Cost per OR Hour],

"Implant % of Cases",format([Implant % of Cases],"#0%;(#0%);-"),

"Implant Cases",sum(Data2[Implant Case]),

"Implant Cost",sum(Data2[Implant Cost]),

"Implant Cost per Case",[Implant Cost per Case],

"Max Cost",max(Data2[VARIABLE]),

"Max Implant Cost",max(Data2[Implant Cost]),

"Min Cost",MIN(Data2[VARIABLE]),

"Min Implant Cost",min(Data2[Implant Cost]),

"MCR % Cases",FORMAT([MCR % Cases],"#0%;(#0%);-"),

"MCR % Charges",FORMAT([MCR % Charges],"#0%;(#0%);-"),

"MCR % Variable Cost",[MCR % Variable Cost],

"Minutes per Case",[Minutes per Case],

"Net Profit",[Net Profit],

"Non Implant Cases",sum(Data2[Non Implant Case]),

"NP per OR Hour",[NP per OR Hour],

"OR Minutes",sum('Time'[VARIANCE]),

"Percent of Cases",[Percent of Cases],

"Reimbursement",[Estimated Reimbursement],

"Roll Avg Cost",[Roll Avg Cost],0),Filter1,Filtert2)

I put together a smaller table to test my soution and it works:

Analysis Measure2:=Var Filter1 = FILTER(Data2,

CONTAINS(VALUES(Groups2[Group Value]),Groups2[Group Value],Data2[FC])||

CONTAINS(VALUES(Groups2[Group Value]),Groups2[Group Value],Data2[Specialty])||

CONTAINS(VALUES(Groups2[Group Value]),Groups2[Group Value],Data2[Age Bucket])||

CONTAINS(VALUES(Groups2[Group Value]),Groups2[Group Value],Data2[Status])||

CONTAINS(VALUES(Groups2[Group Value]),Groups2[Group Value],Data2[Doctor]))

Var Filtert2 = filter(Data2,Data2[Include]="Include")

Return

Switch([Selected Analysis],

"Cases",CALCULATE([Cases],Filter1,Filtert2),

"CM per OR Hour",CALCULATE([CM per OR Hour],Filter1,Filtert2),

"Percent of Cases",DIVIDE(CALCULATE([Cases],Filter1,Filtert2),calculate([Cases],Filtert2),0),0)

In the first formula I was filtering the entire switch formula by filter1 and filter2, but in the second one I put the filters on each line and in the percent of cases line I put both filters on the numerator and only the filter2 on the denominator.  

the only thing is now I need to go back to the first formula, take our the calulation on the entiere switch formula and put the filters on each line.

Do you know of a different solution so that I would not have to enter the filters on each line of the switch?

[BaldAccountant]

@MFelix 

Thanks for your answer

This is the revised formula I hope I got it entered correctly, but I am still getting 100% on alll specialties.

Percent of Cases:=format(divide([Cases],CALCULATE([Cases],ALLSELECTED('Physician Reference'[Specialty 2])),0),"#0%;(#0%);-")

Also the reason I am using format in the calculation is because this formula is used in a switch function to give me a dynamic choice of formula.  There are other formulas in the switch formula such as Average Cost per case of Minutes per Surgery.  The format allows me to change the formatting dynamically.  I could just format is on the line in the switch formula instead, but I chose to do it in the indiviual formulas for percentages.