Using a measure as a scaling factor inside another measure

[V_Low]

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.

[Anonymous · Accepted solution]

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

[V_Low]

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]

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