Forum Discussion
Path Measure
- 1 year ago
Troubleshoot the particular filter context you are concerned with, which are:
Path[L1] = Expense, Path[L2] = Nursing and Medical, Path[L3] = Nursing Other, Data[L4] = {PR Dental Ins, PR WC Ins, Supplies}
Path[L1] = Expense, Path[L2] = Nursing and Medical, Path[L3] = Nursing Wages, Data[L4] = {DC, NDC}
Looking at your measures:
- In Path P&L PPD, you have a switch with two options and then a default fallback. The two options' criteria are not met, so we are calculating the fallback: DIVIDE([Custom Amount2],[Census])
- In Custom Amount2, we likewise have a switch with two options and a default fallback. And again, the first two options' criteria are not met, so we are calculating the fallback: SUM(Data[Amount])
- In Census, there are no conditionals and we just have the straight calculation: CALCULATE(SUM(Data[Amount]),TREATAS({"Census"},'Path'[Level 1]),REMOVEFILTERS('Path'))
In other words, we can simplify your question a bit into, why is the following blank for the filter contexts we enumerated at top?
Simplified = DIVIDE( SUM( Data[Amount] ), CALCULATE( SUM( Data[Amount] ), TREATAS( {"Census"}, 'Path'[Level 1] ), REMOVEFILTERS('Path') ) )For this to be blank, that means the numerator is blank, the denominator is blank, or the denominator is 0. Without seeing your data, we don't know for sure.
That said, here is my guess for what is the problem:
- Given that you seem to expect something to calculate here, probably the issue is the filter context on your denominator, specifically, we have Data[L4] in the filter yet you do not seem to be handling it in any way.
- As in, we have REMOVEFILTERS( 'Path' ), which ensures we ignore the Path-related filter context, but we still have filter context on Data.
- So, as is, the denominator calculation is saying, "give me the sum of Data[Amount] for all rows that fall under Path[L1] = Census (given TREATAS and REMOVEFILTERS) AND where Data[L4] = {DC, NDC, PR Dental Ins, PR WC Ins, Supplies} (given we aren't touching Data's filter context)."
- My guess is that none of your Data rows actually match this filter criteria and thus you are getting blank.
I suspect what you want in the denominator is just the total sum of Data[Amount] under Path[L1] = Census, so this can probably all be fixed by updating your Census measure:
Simplified_fixed? = DIVIDE( SUM( Data[Amount] ), CALCULATE( SUM( Data[Amount] ), TREATAS( {"Census"}, 'Path'[Level 1] ), REMOVEFILTERS('Path'), REMOVEFILTERS(Data) // <-- FIX HERE ) )
Troubleshoot the particular filter context you are concerned with, which are:
Path[L1] = Expense, Path[L2] = Nursing and Medical, Path[L3] = Nursing Other, Data[L4] = {PR Dental Ins, PR WC Ins, Supplies}
Path[L1] = Expense, Path[L2] = Nursing and Medical, Path[L3] = Nursing Wages, Data[L4] = {DC, NDC}
Looking at your measures:
- In Path P&L PPD, you have a switch with two options and then a default fallback. The two options' criteria are not met, so we are calculating the fallback: DIVIDE([Custom Amount2],[Census])
- In Custom Amount2, we likewise have a switch with two options and a default fallback. And again, the first two options' criteria are not met, so we are calculating the fallback: SUM(Data[Amount])
- In Census, there are no conditionals and we just have the straight calculation: CALCULATE(SUM(Data[Amount]),TREATAS({"Census"},'Path'[Level 1]),REMOVEFILTERS('Path'))
In other words, we can simplify your question a bit into, why is the following blank for the filter contexts we enumerated at top?
Simplified =
DIVIDE(
SUM( Data[Amount] ),
CALCULATE(
SUM( Data[Amount] ),
TREATAS( {"Census"}, 'Path'[Level 1] ),
REMOVEFILTERS('Path')
)
)
For this to be blank, that means the numerator is blank, the denominator is blank, or the denominator is 0. Without seeing your data, we don't know for sure.
That said, here is my guess for what is the problem:
- Given that you seem to expect something to calculate here, probably the issue is the filter context on your denominator, specifically, we have Data[L4] in the filter yet you do not seem to be handling it in any way.
- As in, we have REMOVEFILTERS( 'Path' ), which ensures we ignore the Path-related filter context, but we still have filter context on Data.
- So, as is, the denominator calculation is saying, "give me the sum of Data[Amount] for all rows that fall under Path[L1] = Census (given TREATAS and REMOVEFILTERS) AND where Data[L4] = {DC, NDC, PR Dental Ins, PR WC Ins, Supplies} (given we aren't touching Data's filter context)."
- My guess is that none of your Data rows actually match this filter criteria and thus you are getting blank.
I suspect what you want in the denominator is just the total sum of Data[Amount] under Path[L1] = Census, so this can probably all be fixed by updating your Census measure:
Simplified_fixed? =
DIVIDE(
SUM( Data[Amount] ),
CALCULATE(
SUM( Data[Amount] ),
TREATAS( {"Census"}, 'Path'[Level 1] ),
REMOVEFILTERS('Path'),
REMOVEFILTERS(Data) // <-- FIX HERE
)
)- MStark1 year ago
Helper III
MarkLaf Thank you SO much!! Your measure works!! The only thing I added was keepfilters so that can have the dates and location filtered as well
Path P&L PPD fixed =VAR Level1 = SELECTEDVALUE('Path'[Level 1])VAR Level2 = SELECTEDVALUE('Path'[Level 2])VAR Amount = [Custom Amount2]VAR MatchingCensus = [MatchingCensus]VAR DaysInMonth = 28VAR TotalCensus =CALCULATE(SUM(Data[Amount]),TREATAS({"Census"}, 'Path'[Level 1]),REMOVEFILTERS('Path'),REMOVEFILTERS(Data),KEEPFILTERS(VALUES(Data[Posting_Date])),KEEPFILTERS(VALUES(Data[Location_Name])) )RETURNSWITCH(TRUE(),Level1 = "Census", DIVIDE(Amount, DaysInMonth),Level1 = "Revenue" && Level2 = "Revenue Current", DIVIDE(Amount, MatchingCensus),DIVIDE(Amount, TotalCensus))
Ive been playing around with this for over 3 days and couldnt figure it out (chatgpt also tried but couldnt get it;)) Really appreciate you taking the time to look at this and figure it out!!