Forum Discussion

Hoho1's avatar
Hoho1
Frequent Visitor
8 years ago
Solved

Measure should stay fixed

Hello, Is there any way to create a measure that holds two values that stay fixed when you select other filters? I have the following table:   Bads Count 0 1200 1 100 Total 1300 ...
Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    Hoho1

     

    I am assuming you have separate columns for numerator and denominator.

    If so, you can use the following -

    Original % Measure = IF(HASONEVALUE(Table5[bad]),
    CALCULATE(SUM(Table5[Count1]), ALLEXCEPT(Table5, Table5[bad])) / CALCULATE(SUM(Table5[Count2]), ALLEXCEPT(Table5, Table5[bad])),
    CALCULATE(SUM(Table5[Count1]), ALLEXCEPT(Table5, Table5[bad])) / CALCULATE(SUM(Table5[Count2]), ALL(Table5))
    )

     

    Mainly, HASONEVALUE will treat your individual rows v/s. Total row differently. Hope it helps.

     

    Regards

anandav's avatar
anandav
Icon for Skilled Sharer rankSkilled Sharer
8 years ago

Can you try:

 

Original % Measure = CALCULATE(SUM(Table5[Count]), ALLEXCEPT(Table5, Table5[bad])) / CALCULATE(SUM(Table5[Count]), ALL(Table5))

  • Hoho1's avatar
    Hoho1
    Frequent Visitor
    8 years ago

    Thanks for your response anandav however your denominator gets fixed to (based on my example) 1300 which is not quite what I want. I need it to be equal to 1200 when Bads=0 and equal to 100 when Bads=1. It's on the right path though! :)