Showing results for 
Search instead for 
Did you mean: 
MSVALE Frequent Visitor
Frequent Visitor

Changing Measure Formula when using Slicer

Hello guys,

i created a table with 2 rows only called "ChurnType" with "Type" inside that only has "Partner and Customer"


This is my measure:

Churn Rate = IF([#Churn] = 0, 0, ([#Churn] + [#Lost]) / ([#Renewable] + [#Lost]))
And what i want to do is:
Churn Rate = IF([#Churn] = 0, 0, ([#Churn] + IF(ChurnType[Type] = "Partner", 0, [#Lost])) / ([#Renewable] + IF(ChurnType[Type] = "Partner", 0, [#Lost])))
But gives me the error "a single value for column "Type" in table... cannot be determined... column tahat contains many values..."
What i want is when i select option in slicer the formula changes because is differente from partner to customer.
I would apreciate some help on how to solve this.
Thanks in advance
TOK Frequent Visitor
Frequent Visitor

Re: Changing Measure Formula when using Slicer

You need to use aggregation to deal with this problem. There are many possibilities to solve this.
1. create a new column in the data table
2. create a numeric ChurnType and use an aggregate function (Sum()) creating the measure
3. Adapt the formula using something like this:
ChurnRate =  SUMX(#;
    IF ( #[ChurnType] = "Customer"
; IF(Sum(#[Churn])= 0;0;(Sum(#[Churn])+Sum(#[Lost]))/(Sum(#[Renewable])+Sum(#[Lost])))
; IF(Sum(#[Churn]) = 0; 0 ; Sum(#[Churn])/Sum(#[Renewable]))))
I am not quite sure, what exactly you want to compute. So this is my solution, hope it helps.

Helpful resources

Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

Top Ideas
Users Online
Currently online: 31 members 927 guests
Please welcome our newest community members: