cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
V_Low
Frequent Visitor

Using a measure as a scaling factor inside another measure

Hello, I tried going through similar threads, but found (or at least didn't manage to succeed in implementing) no solution to fit my problem.

I have a table of pass/fail data with unique case keys and failure categories, see below:

Key    Fail   Category     Subcategory
Key 1 x Category 1 Subcategory 1
Key 2
Key 3 x Category 2 Subcategory 4
Key 4

I have the following measures currently in use:

First, the fail and success measures

m_Success_% = 
DIVIDE(
COUNTROWS(FILTER(Sheet1;Sheet1[Fail] = ""));
COUNTROWS(Sheet1)
)
m_Fail_% = 
1-[m_Success_%]

I would like to have a measure with which I could have a bar chart showing the effect of different categories to the failure %. The closest I have gotten is by cheating in the sense that if I know my failure % is 45%, I just use the below measure with hardcoded scaling factor of 0.45.

m_check_% = 
DIVIDE(
COUNTROWS(Sheet1);
COUNTROWS(ALLSELECTED(Sheet1))
) * 0.45

I tried referencing the [m_Fail_%] in the last measure, but that does not seem to work.

m_category_% = 
DIVIDE(
COUNTROWS(Sheet1);
COUNTROWS(ALLSELECTED(Sheet1))
) * CALCULATE([m_Fail_%];ALLSELECTED(Sheet1))

 

Point being, that when I get new cases, the fail % will change and I would like the chart on the right always to sum up to the current fail %.

I attached a screenshot of the situation, hopefully that clarifies what I'm after.

 

Thanks.2020-05-15 07_59_20-Fail_cases - Power BI Desktop.png

1 ACCEPTED SOLUTION
Anonymous
Not applicable

// The model is incorrect in the first place.
// A good model is a star schema. You should
// have tables of Cases and Categories/Subcategories.
// Then, a fact table that would join one to the other.
// Then the measures to write would be easier
// and more logical. For this flawed model, you
// could use the below measures but handling
// categories and subcategories is not optimal
// since you have to hard-code the columns into the
// measure.

// measure 1
[Failure Rate] =
var __failures =
	calculate(
		countrows( T ),
		keepfilters(
			T[Fail] = "x"
		)
	)
var __allVisible =
	calculate(
		countrows( T ),
		ALL( T[Category], T[Subcategory] )
	)
var __result =
	divide(
		__failures,
		__allVisible
	)
return
	__result
	
// measure 2
[Success Rate] = 1 - [Failure Rate]

 

Best

D

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

// The model is incorrect in the first place.
// A good model is a star schema. You should
// have tables of Cases and Categories/Subcategories.
// Then, a fact table that would join one to the other.
// Then the measures to write would be easier
// and more logical. For this flawed model, you
// could use the below measures but handling
// categories and subcategories is not optimal
// since you have to hard-code the columns into the
// measure.

// measure 1
[Failure Rate] =
var __failures =
	calculate(
		countrows( T ),
		keepfilters(
			T[Fail] = "x"
		)
	)
var __allVisible =
	calculate(
		countrows( T ),
		ALL( T[Category], T[Subcategory] )
	)
var __result =
	divide(
		__failures,
		__allVisible
	)
return
	__result
	
// measure 2
[Success Rate] = 1 - [Failure Rate]

 

Best

D

View solution in original post

V_Low
Frequent Visitor

Thank you very much for your fast reply, I got the functionality I was after!

Additional thanks for giving advice on future improvements, this kind of data structure thinking is new to me so I really appreciate the explanation on recommended way to structure my model 🙂

 

Anonymous
Not applicable

As I said, the model is wrong. Try to put a filter on the Fail column and select "x." See what happens with the above measures and try to explain the result (yes, it'll make sense). Also, try to select the blank value and explain the numbers. It'll be a good exercise for your brain 🙂

Please always create good dimensional models. If you don't, you'll be constantly getting yourself into trouble. GUARANTEED.

Best
D

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors