Measure with ALL

[Leasie_7]

I am in need of some expert help. I am very confused as I have not had this problem arise yet, though still relatively new to Power BI. To me it seems to be quirky...? -- Thanks in advance!

Below is similiar to what the end result will look like; I would show top 3, but my issue is within "Outreach to Provider". Specifically my issue I have lies within the denominator that's used to calculate the percentages below.

For the screenshot below I am using this measure directly mentioned below to get the correct denominator for my % calculation which I don't want to use because it does not allow my end user to change the filter on "Outcome" if they desire. 

Total Resolved RCA (Ignore Level 2) for Overturned Only = CALCULATE([Total Resolved RCA],ALL(RESOLVED[Level 2]),KEEPFILTERS(RESOLVED[Outcome]<>"Upheld"))

I would have thought I could have used this measure with ALL just I have have for other visuals, but it appears to act strangely and I do not know why: Total Resolved RCA (Ignore Level 2) = CALCULATE([Total Resolved RCA],ALL(RESOLVED[Level 2]))

I figured the screen shots below better show the components, my issue is why is the last column; my desired measure, is not showing "54" for all rows when filters or Corrective Action is in the matrix?

With "Corrective Action" as a Row in matrix with filter on visual to only show “Outreach to Provider” why does the last column not all show 54?

Same thing happens when I leave Corrective Action as a row in matrix and remove Corrective Action filter on visual

When I removed Corrective Action from the Row Section, but leave the visual filter on the last column now shows 54 appropriately. This however does not work for my desired result I need to be able to leave "Corrective Action" as a row on the matrix and filter on the visual, but retain "54".

Please any help is appreciated!

[Greg_Deckler]

@Leasie_7 Try:

Total Resolved RCA (Ignore Level 2) for Overturned Only = CALCULATE([Total Resolved RCA],ALLSELECTED('RESOLVED'),KEEPFILTERS(RESOLVED[Outcome]<>"Upheld"))

[Leasie_7]

@Greg_Deckler 

I updated your measure to not have the outcome <> upheld because I want the user to be able to look at upheld if they would like. 

Total Resolved RCA (Ignore Level 2) = CALCULATE([Total Resolved RCA],ALLSELECTED(RESOLVED))

It works when only one corrective action is selected 

However, when I bring in another they get the total between the 2. How do I get "Outreach to Provider" to reflect 54 and "Account Update" to show 45 so I can use it as the denominator for the middle % column on each corrective action category?

I am confused as to why I cannot use "ALL" with Level 2 instead of "ALLSELECTED" for the whole table. Shouldn't it see the visual filter context and apply based on whichever outcomes are selected along with corrective action selected to show in the visual. Maybe I need to use a combination of things to keep corrective action row context, but allow filtering on the whole "Resolved" table as well??

Thanks again

[Leasie_7]

@Greg_Deckler 

Something else quirky is my original measure of:

Total Resolved RCA (Ignore Level 2) = CALCULATE([Total Resolved RCA],all(RESOLVED[Level 2]))

has no issue with getting the correct value until you collapse a corrective action 

correct:

incorrect after collapsing "Claim Adjustment/Reprocessed"

I feel like there is just something goofy going on.

[Greg_Deckler]

@Leasie_7 Really hard to troubleshoot unless you can provide some sample data so that I can mock it up. However, regarding the measure not having the right value when you collapse...

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

[Leasie_7]

Number	Corrective Action	Level 2	Outcome
35739	Account Update	Accounting	Overturned - Error
35349	Account Update	Accounting	Overturned - Error
33849	Account Update	Accounting	Overturned - New Information
33849	Account Update	Accounting	Overturned - New Information
35230	Account Update	Accounting	Overturned - New Information
33824	Account Update	Accounting	Overturned - Error
34540	Account Update	Claims Processing	Overturned - New Information
35201	Account Update	Local Pharmacy	Overturned - New Information
35150	Account Update	Order Processing	Upheld
34977	Account Update	Order Processing	Upheld
34977	Account Update	Accounting	Upheld
35695	Account Update	Order Processing	Overturned - Error
35609	Account Update	Order Processing	Overturned - New Information
35386	Account Update	Order Processing	Overturned - New Information
35075	Account Update	Order Processing	Overturned - New Information
33921	Account Update	Accounting	Overturned - Error
34133	Account Update	Order Processing	Overturned - New Information
33849	Account Update	Order Processing	Overturned - New Information
35465	Account Update	Order Processing	Overturned - New Information
34560	Account Update	Order Processing	Overturned - Error
34476	Account Update	Order Processing	Overturned - New Information
34377	Account Update	Order Processing	Overturned - New Information
34679	Account Update	Web Portal	Upheld
34679	Account Update	Web Portal	Upheld
35746	Account Update	Order Processing	Overturned - New Information
34121	Account Update	Accounting	Overturned - New Information
35201	Account Update	Plan Benefit	Overturned - New Information
34537	Account Update	Plan Benefit	Overturned - New Information
34954	Account Update	Order Processing	Upheld
34612	Account Update	Website/portals	Upheld
35709	Account Update	Prior Auth	Overturned - New Information
34560	Account Update	Prior Auth	Overturned - Error
35265	Account Update	Override	Upheld
35263	Account Update	Order Processing	Upheld
35263	Account Update	Order Processing	Upheld
35263	Account Update	Web Portal	Upheld
34148	Account Update	Prior Auth	Overturned - New Information
34462	Account Update	Eligibility	Upheld
35739	Account Update	Web Portal	Overturned - Error
34355	Account Update	Website/portals	Overturned - New Information
35012	Outreach to Provider	Order Processing	Upheld
35808	Account Update	Order Processing	Upheld
34606	Outreach to Provider	Order Processing	Overturned - New Information
35661	Account Update	Plan Benefit	Upheld
33945	Account Update	Eligibility	Overturned - New Information
34068	Account Update	Eligibility	Overturned - Error
35043	Outreach to Provider	Plan Benefit	Overturned - New Information
35048	Account Update	Order Processing	Upheld
35011	Account Update	Order Processing	Upheld
34143	Outreach to Provider	Prior Auth	Overturned - New Information
35775	Account Update	Order Processing	Overturned - New Information
34824	Account Update	Eligibility	Upheld
34824	Account Update	Order Processing	Upheld
35651	Account Update	Order Processing	Overturned - New Information
34335	Outreach to Provider	Claims Processing	Upheld
34335	Outreach to Provider	Order Processing	Upheld
35795	Outreach to Provider	Prior Auth	Upheld
34538	Outreach to Provider	Order Processing	Overturned - New Information
34876	Account Update	Order Processing	Overturned - Error
34876	Account Update	Order Processing	Overturned - Error
35876	Outreach to Provider	Prior Auth	Upheld
34611	Outreach to Provider	Prior Auth	Overturned - New Information
35756	Outreach to Provider	Prior Auth	Upheld
35663	Outreach to Provider	Prior Auth	Upheld
35578	Outreach to Provider	Prior Auth	Upheld
34335	Outreach to Provider	Web Portal	Upheld
34293	Account Update	Order Processing	Upheld
35604	Outreach to Provider	Claims Processing	Overturned - New Information
35435	Outreach to Provider	Local Pharmacy	Upheld
35604	Outreach to Provider	Local Pharmacy	Overturned - New Information
34096	Outreach to Provider	Prior Auth	Overturned - New Information
34096	Outreach to Provider	Prior Auth	Overturned - New Information
35544	Outreach to Provider	Prior Auth	Overturned - New Information
35843	Outreach to Provider	Order Processing	Overturned - New Information
33828	Outreach to Provider	Prior Auth	Overturned - New Information
34862	Outreach to Provider	Prior Auth	Overturned - New Information
35043	Outreach to Provider	Prior Auth	Overturned - New Information
35757	Outreach to Provider	Prior Auth	Overturned - New Information
34528	Outreach to Provider	Prior Auth	Overturned - New Information
35142	Outreach to Provider	Prior Auth	Overturned - New Information
34129	Account Update	Order Processing	Overturned - Error
34365	Outreach to Provider	Order Processing	Overturned - New Information
34365	Outreach to Provider	Order Processing	Overturned - New Information
34129	Account Update	Order Processing	Overturned - Error
35481	Outreach to Provider	Plan Benefit	Overturned - New Information
33821	Outreach to Provider	Prior Auth	Overturned - New Information
33821	Outreach to Provider	Prior Auth	Overturned - New Information
34310	Outreach to Provider	Prior Auth	Overturned - New Information
34310	Outreach to Provider	Prior Auth	Overturned - New Information
35195	Outreach to Provider	Prior Auth	Overturned - New Information
34531	Outreach to Provider	Order Processing	Overturned - New Information
33819	Outreach to Provider	Prior Auth	Upheld
33997	Outreach to Provider	Prior Auth	Overturned - New Information
34047	Outreach to Provider	Prior Auth	Overturned - New Information
34262	Outreach to Provider	Prior Auth	Overturned - New Information
34531	Outreach to Provider	Prior Auth	Overturned - New Information
35646	Outreach to Provider	Customer Service	Overturned - Error
35590	Outreach to Provider	Prior Auth	Overturned - New Information
33911	Outreach to Provider	Order Processing	Overturned - New Information
35777	Outreach to Provider	Order Processing	Overturned - New Information
33982	Outreach to Provider	Prior Auth	Overturned - New Information
34247	Outreach to Provider	Prior Auth	Overturned - New Information
35378	Account Update	Web Portal	Upheld
34139	Outreach to Provider	Appeals	Overturned - New Information
33904	Account Update	Order Processing	Overturned - Error
34946	Outreach to Provider	Order Processing	Overturned - New Information
34972	Account Update	Order Processing	Upheld
34972	Account Update	Programs	Upheld
34645	Account Update	Web Portal	Upheld
35071	Account Update	Order Processing	Overturned - New Information
34040	Account Update	Order Processing	Overturned - New Information
35360	Account Update	Outreach	Overturned - New Information
35104	Outreach to Provider	Order Processing	Overturned - New Information
33898	Outreach to Provider	Prior Auth	Overturned - New Information
34993	Outreach to Provider	Prior Auth	Upheld
34155	Outreach to Provider	Prior Auth	Overturned - New Information
34245	Outreach to Provider	Prior Auth	Overturned - New Information
33864	Outreach to Provider	Order Processing	Overturned - New Information
34147	Outreach to Provider	Prior Auth	Overturned - New Information
35595	Account Update	Prior Auth	Overturned - New Information
34941	Account Update	Website/portals	Upheld
34129	Account Update	Prior Auth	Overturned - Error
34589	Outreach to Provider	Prior Auth	Overturned - New Information
35546	Outreach to Provider	Prior Auth	Overturned - New Information
35341	Outreach to Provider	Plan Benefit	Overturned - New Information
35141	Outreach to Provider	Prior Auth	Overturned - New Information
35141	Outreach to Provider	Prior Auth	Overturned - New Information
33944	Outreach to Provider	Order Processing	Overturned - New Information
33858	Outreach to Provider	Prior Auth	Overturned - New Information
33967	Outreach to Provider	Prior Auth	Overturned - New Information
34957	Account Update	Eligibility	Upheld
34027	Outreach to Provider	Prior Auth	Overturned - New Information
34289	Account Update	Prior Auth	Overturned - New Information
33835	Outreach to Provider	Order Processing	Overturned - New Information
34144	Account Update	Order Processing	Upheld
35441	Account Update	Web Portal	Overturned - New Information
34532	Account Update	Web Portal	Overturned - Error

@Greg_Deckler  Sorry I did not know how to get sample data on this post. Below is my attempt from the above sample data and you can see on the right when I collapse it does not hold. 

[Greg_Deckler]

@Leasie_7 One question, what is the formula for "Total Resolved RCA" measure?

[Leasie_7]

@Greg_Deckler 

The main Total Resolved RCA measure is 

Total Resolved RCA = COUNTA('RESOLVED'[Number])

The measure that I would expect to work with allowing filtering on outcome still is:

Total Resolved RCA (Ignore Level 2) = CALCULATE([Total Resolved RCA],all(RESOLVED[Level 2]))

[Greg_Deckler]

@Leasie_7 No need to apologize, that is literally perfect. Copies and pastes into an Enter Data query just fine. Let me see what I can mock up.