Converting Measure Value to create categories and use them on axis-Power BI

[sumitsingla12]

Dear All,

I hope anyone of you would be able to help me on one of the issue, i am stuck with.

MemberID	Fund Code	Current Revenue	Future Revenue	Impact (Future-Current)	Category
M1	F1	20	15	-5
M1	F2	35	40	5
M1	F3	40	55	15
M2	F2	55	45	-10
M2	F3	65	85	20

I want to create a visual like below where on x axis i want to show categories and y axis i want to show no of members in each categories.

Categories should if Field Impact (Future-Current)>0 then "Worse" else "Better".

So my solution i created a measure impact at member level using below calculation.

Member Level Impact =

CALCULATE(SUM('data6 (2)'[Impact (Future-Revenue)]),FILTER(ALLSELECTED('data6 (2)'),'data6 (2)'[MemberID]=max('data6 (2)'[MemberID])))

On top of this measure i have created a calculated column which would be my x-axis.

Member Lvl Category = if([Member Level Impact]>0,"Worse","Better")

This scenario is working fine but when i am applying any slicer filter for fund it is still calculating category for whole Member.

Do we have any specific way to achive this.

Requirement : we need to create categories based on measure value which should be considered after applying slicers. 

In above case Member M1 would be Better if we have selection F1 from Fund slicer,

same will be worse if we have not done any selection.

Can you please help, we can connect if required.

[danextian · Accepted solution]

Hi @sumitsingla12 

I would create a separates table that holds the better/worse column and use this in a measure to be able to filter my data. Please see attached sample pbix .

[sumitsingla12]

Hi Dane,

Coming back to you again for another problem in continuation of this thread.

I am trying to create another visual in same report to show worse off members at fund level.

So visual would worse off member at fund level. A single member may be in multiple funds , in that it would counted twice in both the funds.

We need to check member level impact and then count that member in respective fund. 

I have tried the same logic but my logic is considering fund -member value and then defining whether member is worseoff or not.

Worse off calculation should be irrespective of fund.

From below table if any slicer filter is not applied then F1 should have 3 members as worse off because for all three members fee impact is >0.

Similarly F2 should have 2 worse off members etc.

Am getting below results, reason is it is calculating member fee impact for each fund not at member level.

for F1 it is not considering M2 because F1-M2 combination .

Calculated measure created in original table.

Worse off Members = CALCULATE(DISTINCTCOUNT('Sample Data'[MemberID]),FILTER('Sample Data',[Member Fee Impact]>0))

Member Fee Impact = CALCULATE(SUM('Sample Data'[Future Revenue])-SUM('Sample Data'[Current Revenue]),FILTER(ALLSELECTED('Sample Data'),'Sample Data'[MemberID]=max('Sample Data'[MemberID])))

[sumitsingla12]

Thanks Mate, this resolves my query. Really appreciate your quick response

[sumitsingla12]

Hi @grazitti_sapna ,

Thanks for quick response, but it is not working. You could see below example where i have selected F1 from fund slicer and For this selection Member ID M1 should be good.

but in any case it is showing worse.

When i select all funds then it shuld be worse.

[grazitti_sapna]

Hi @sumitsingla12,

You welcome. You can try one more thing. i.e create a measure first for Member level impact

Member Level Impact = SUM('data6 (2)'[Impact (Future-Current)])

like this and thn calculate the no of members for each categore.
Members Count =
VAR SelectedFunds =
VALUES('data6 (2)'[Fund Code])
VAR ImpactCategory =
IF([Member Level Impact] > 0, "Worse", "Better")
RETURN
CALCULATE(
DISTINCTCOUNT('data6 (2)'[MemberID]),
FILTER(
ALL('data6 (2)'),
IF([Member Level Impact] > 0,
CALCULATE(
COUNTROWS(INTERSECT(SelectedFunds, VALUES('data6 (2)'[Fund Code]))),
'data6 (2)'[Impact (Future-Current)] > 0
),
CALCULATE(
COUNTROWS(INTERSECT(SelectedFunds, VALUES('data6 (2)'[Fund Code]))),
'data6 (2)'[Impact (Future-Current)] <= 0
)
) > 0 && ImpactCategory = [Member Lvl Category]
)
)

And thn

Step 3: Create a measure to determine the category based on the slicer selections:

Like this:-

Member Lvl Category =
IF([Member Level Impact] > 0, "Worse", "Better")

It should work. Let me know if you have any questions.

[grazitti_sapna]

Hi @sumitsingla12 ,

As per my understanding, You can try using.

Member Level Category =
VAR SelectedFunds =
VALUES('data6 (2)'[Fund Code])
RETURN
IF(
[Member Level Impact] > 0,
IF(
COUNTROWS(INTERSECT(SelectedFunds, VALUES('data6 (2)'[Fund Code]))) > 0,
"Worse",
"Better"
),
IF(
COUNTROWS(INTERSECT(SelectedFunds, VALUES('data6 (2)'[Fund Code]))) > 0,
"Better",
"Worse"
)
)

Hope this will help.