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

Anonymous's avatar
Anonymous
Not applicable
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

Hoho1's avatar
Hoho1
Frequent Visitor
8 years ago

A million thanks Anonymous! Your solution did exactly what I wanted! I can't thank you enough! :smileyvery-happy::smileyvery-happy: