Forum Discussion

Kay_C's avatar
Kay_C
New Member
2 years ago

Using a Measure with a Numeric Slicer that includes the measure's denominator?

Hopefully this is easy, but I've been searching for days and have not found it in the forums... 

1. Desired Result: Tell me what my profitability is for a variety of regional and revenue buckets. So if I have a profitability of 10% overall, does that profitability change when my deal is worth $1M in revenue vs $20M in revenue? Does that change by region or leader? 

2. The Problem: I can use the regional and leader slicers, but when I use the revenue slicer, the measure returns a value of 100%.

3. The Measure: I'm calculating profit as a percentage of revenue. So, basically profit/revenue. I have several slicers so I can see the profit by leader or region, etc. The measure and its interaction with these slicers is fine. 
Profit Percentage = CALCULATE(DIVIDE(SUM('CC'[Profit]), SUM('CC'[Rev]), 0), ALLSELECTED())

4. The Problem Slicer: The slicer that is a problem is a revenue slicer, again trying to answer: is our profitability better on smaller deals or bigger deals? The slicer is simply the revenue. I think the issue is that the slicer is also the denominator of the measure. 

 

Troubleshooting so far, I have tried to used a variety of calculations for the divide function including removing the calculate, divide, and adding the if, then functions, as well as a few of the filter choices. The interaction with the revenue slicer remains the same where it returns 100% when I use it. I have also tried to change the slicer, I used another numeric slicer and it was fine and the other text slicers were also fine. In a final effort, I also tried the same variety of formulas as a column instead. 
 
Please help 🙂 

1 Reply

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    2 years ago

    You cannot measure a measure.  Your revenue slicer must be fed from a (pre(calculated)) column.