Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
ThomasWeppler
Post Prodigy
Post Prodigy

ABC customer

Hi Power BI community

I try to make a ABC analasic of our customers. I have used this simple DAX-code:

ABC =
switch(TRUE(), ISINSCOPE('Hoved kunder'[Kunde]),
    IF (
    'Measure'[Faktureret] > 250000
        && [Dækningsgrad] > 0.3,
        "AA",
    IF (
    'Measure'[Faktureret] > 250000
       && [Dækningsgrad] <= 0.3
       && [Dækningsgrad] > 0.15,
       "AB",
    IF (
    'Measure'[Faktureret] > 250000
        && [Dækningsgrad] <= 0.2,
        "AC",
    IF (
    'Measure'[Faktureret] <= 250000
        && 'Measure'[Faktureret] > 50000
        && [Dækningsgrad] > 0.3,
        "BA",
    IF (
    'Measure'[Faktureret] <= 250000
        && 'Measure'[Faktureret] > 50000
        && [Dækningsgrad] <= 0.3
        && [Dækningsgrad] > 0.15,
        "BB",
    IF (
        'Measure'[Faktureret] <= 250000
        && 'Measure'[Faktureret] > 50000
        && [Dækningsgrad] <= 0.15,
        "BC",
    IF (
        'Measure'[Faktureret] <= 50000
        && [Dækningsgrad] > 0.3,
        "CA",
    IF (
        'Measure'[Faktureret] <= 50000
        && [Dækningsgrad] <= 0.3
        && [Dækningsgrad] > 0.15,
        "CB",
        "CC")))))))))




My problem is that I cannot use is as a filter to find all the customers in diffrent categories and I cannot use it as a visual. Proberly because I use the swift is true function. Is there a way where I can gette the same results, but I will be able to use the measure in visuals and/or as a filter?

All help is greatly appreciated.

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@ThomasWeppler , Please refer if the Window function can help

Power BI ABC Analysis using Window function, Dynamic Segmentation: https://youtu.be/A8mQND2xSR4

 

View solution in original post

4 REPLIES 4
v-xiandat-msft
Community Support
Community Support

Hi @ThomasWeppler ,

To achieve your goal, consider creating calculated columns instead of metrics. Calculated columns are computed and stored in the model during data refresh, making them suitable for use as filters or visual object axes.

You can try this:

 

ABC Category = 
SWITCH(
    TRUE(),
    'Table'[Faktureret] > 250000 && 'Table'[Dækningsgrad] > 0.3, "AA",
    'Table'[Faktureret] > 250000 && 'Table'[Dækningsgrad] <= 0.3 && 'Table'[Dækningsgrad] > 0.15, "AB",
    'Table'[Faktureret] > 250000 && 'Table'[Dækningsgrad] <= 0.2, "AC",
    'Table'[Faktureret] <= 250000 && 'Table'[Faktureret] > 50000 && 'Table'[Dækningsgrad] > 0.3, "BA",
    'Table'[Faktureret] <= 250000 && 'Table'[Faktureret] > 50000 && 'Table'[Dækningsgrad] <= 0.3 && 'Table'[Dækningsgrad] > 0.15, "BB",
    'Table'[Faktureret] <= 250000 && 'Table'[Faktureret] > 50000 && 'Table'[Dækningsgrad] <= 0.15, "BC",
    'Table'[Faktureret] <= 50000 && 'Table'[Dækningsgrad] > 0.3, "CA",
    'Table'[Faktureret] <= 50000 && 'Table'[Dækningsgrad] <= 0.3 && 'Table'[Dækningsgrad] > 0.15, "CB",
    "CC"
)

The second solution is applying measure to visual filter

vxiandatmsft_0-1718084276266.png

 

Best Regards,

Xianda Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thanks for the answer @v-xiandat-msft The problem with an added colum is that I cannot affect it with my other filters in the repport.
I will try to see if @amitchandak video can help me otherwise I will go with the second way to add measures as you suggested. 

Thanks both of you for the helpful answers.

amitchandak
Super User
Super User

@ThomasWeppler , Please refer if the Window function can help

Power BI ABC Analysis using Window function, Dynamic Segmentation: https://youtu.be/A8mQND2xSR4

 

I finnaly got time to look at it. It is a good idea to make a new table as you suggest in the video and use that as a way to filter the table.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.