cancel
Showing results for
Search instead for
Did you mean:
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
1 REPLY 1
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

Announcements

#### Back to School Contest

Engage and empower students with Power BI!

#### Watch Sessions On Demand!

Continue your learning in our online communities.

#### Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

#### PBI Community Highlights

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

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