Forum Discussion

geivaz0606's avatar
geivaz0606
Regular Visitor
4 years ago
Solved

Division Measure with special case

Good Day,

I hope someone can help me with this request.  It seems like a simple problem but I am stumped as to how to handle this.

 

I have a simple dataset.  It looks something like this.

 

IDRisk StatusNumeratorDenominatorProgress - Measure (Ratio)Prefered Method (Division only for On-Risk)
111On-Risk12150.80000.8000
222Off-Risk14150.93331
333Inactive0200.00000
444On-Risk1150.06670.0667
555On-Risk22250.88000.8800
666Off-Risk551.00001
777Inactive0300.00000

 

What I am needing the is the last column.  We have a table with the columns 1-4.  Column 5 is just a simple measure that divides column 3 into 4.  Basically to get a ratio of how much progress has been made a ratio.

The measure is written as such below

Progress Measure = DIVIDE(SUMX('Fact', 'Fact'[Numerator]), SUMX('Fact', 'Fact'[Denominator]), 0 )
 
This measure works as intended.  The problem is that I don't want the division on this to work on every row like this.  I only want that rule to apply to rows that have the "On-Risk" value in column 2 (Risk Status). 
Otherwise, if Column 2 happens to be "Inactive", I would like to see a 0, else if column 2  "Off-Risk", I would like a 1 to come back for that row.
 
The reason for this is that ultimately, we want a ratio that should be 1 (100%) and 0 (0%) for the Off-Risk and Inactive rows respectively.
 
Any help with this will be very appreciated.
George
DAX
measure
Switch()
  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

    It's not clear to me what the desired result should be in such a case. Are you trying to calculate an average iterating over the rows of the fact table?

     

    If so, just stick it inside an AVERAGEX.

    Preferred Method =
AVERAGEX (
    'Fact',
    SWITCH (
        'Fact'[Risk Status],
        "On-Risk", [Progress Measure],
        "Inactive", 0,
        "Off-Risk", 1
    )
)

3 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    4 years ago

    This is fairly straightforward with a SWITCH.

    Preferred Method =
SWITCH (
    SELECTEDVALUE ( 'Fact'[Risk Status] ),
    "On-Risk", [Progress Measure],
    "Inactive", 0,
    "Off-Risk", 1
)

     

    • geivaz0606's avatar
      geivaz0606
      Regular Visitor
      4 years ago

      Alexis, Thank you for the response.  You are correct in that your response works for the problem I described.  However, in my in-experience, I did not describe the problem very well.  

      Good Response

       

      My problem is that I did think about what would happen when this Measure gets used in other areas.  For example, when we look at the full dataset, there is also a year. And if we group by that in a Matrix, we don't get the desired result.

       

      Would you happen to know how that could be solved, please?

      Btw, if you want, I will mark your previous answer as "Accept as Solution" if you so desire.

       

      Thank you in advance.

      George

      • AlexisOlson's avatar
        AlexisOlson
        Super User
        4 years ago

        It's not clear to me what the desired result should be in such a case. Are you trying to calculate an average iterating over the rows of the fact table?

         

        If so, just stick it inside an AVERAGEX.

        Preferred Method =
AVERAGEX (
    'Fact',
    SWITCH (
        'Fact'[Risk Status],
        "On-Risk", [Progress Measure],
        "Inactive", 0,
        "Off-Risk", 1
    )
)