Forum Discussion
Based on Companies, show Group Status
Hi all,
I have a company table as below:
We have a lot of companies put in groups. For each company we show number of days since last purchase, and then we put them in a churn category. However, how would I make my column E? Since if I put it in a graph, "Team A" would be placed in all 3 categories, but I would only want it to be in A Category.
Thanks!
6 Replies
- mahoneypat
Microsoft Employee
Is Churn Category a measure? If so, you can probably just use the measure below, replacing Table for your actual table name. If it is not a measure, a SWITCH expression is likely needed.
Group Churn = CALCULATE([Churn Category], ALL(Table[Company Name]))
Regards,
Pat
Hi,
No it is a calculated column:
Churn Category = IF(Companies[Churn Days]<60,"A) 0-59 Days",IF(Companies[Churn Days]<120,"B) 60-119 Days",IF(Companies[Churn Days]<180,"C) 120-179 Days",IF(Companies[Churn Days]<=365,"D) 180-365 Days",IF(Companies[Churn Days]<545,"E) +366 Days","F) +545 Days")))))So I would like to do a calculated column, that shows the "minimum" Churn of a Company Group
- amitchandak
Super User
Anonymous , I think the larger picture is not clear, try a new column
if([group name] ="Team A", "A",[churn Category])
- mahoneypat
You can first create a variable to get the MIN value across all companies in the group and then use that variable throughout your expression (instead of the column name). It also would be better to use SWITCH(TRUE(), ... instead of nested IFs.
Churn Category =
VAR vGroupMin =
CALCULATE (
MIN ( Companies[Churn Days] ),
ALLEXCEPT (
Companies,
Companies[Company Group]
)
)
RETURN
SWITCH (
TRUE (),
vGroupMin < 60, "A) 0-59 Days",
vGroupMin < 120, "B) 60-119 Days",
vGroupMin < 180, "C) 120-179 Days",
vGroupMin <= 365, "D) 180-365 Days",
vGroupMin < 545, "E) +366 Days",
"F) +545 Days"
)Regards,
Pat
