Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
geivaz0606
Regular Visitor

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
1 ACCEPTED SOLUTION

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
    )
)

View solution in original post

3 REPLIES 3
AlexisOlson
Super User
Super User

This is fairly straightforward with a SWITCH.

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

 

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 ResponseGood 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?

Needs Work IMage.PNG

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

 

Thank you in advance.

George

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
    )
)

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors