Forum Discussion

ThomasWeppler's avatar
ThomasWeppler
Impactful Individual
2 years ago
Solved

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.

Need Help

4 Replies

    • ThomasWeppler's avatar
      ThomasWeppler
      Impactful Individual
      2 years ago

      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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      ThomasWeppler
      Impactful Individual
      2 years ago

      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.