ABC customer

[ThomasWeppler]

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.

[Anonymous]

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

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.

[ThomasWeppler]

Thanks for the answer @Anonymous 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 · Accepted solution]

@ThomasWeppler , Please refer if the Window function can help

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

[ThomasWeppler]

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.