Forum Discussion

SabineOussi's avatar
SabineOussi
Skilled Sharer
4 years ago

Measure returning majority category

Hello,

 

I'm trying to categorize my products based on Sales and Cost conditions.

Based on the majority of the products category, we are then categorizing the manufacturer.

 

 

 

This is my product category condition: 
ProductCategory =IF(AND(C2>=200, D2<=5), "CAT1", IF(AND(C2>=100, D2<=10), "CAT2", IF(AND(C2>=50, D2<=20), "CAT3", "CAT4")))

 

Following an Excel simulation, this is my manufacturer category condition:

=COUNTIF($E$2:$E$7,G2)

 

So for example for Manufacturer A, most of the products are categorized as CAT3 so manufacturer A is labeled CAT3.

 

I want to achieve the same in Power BI where the manufacturer category is a measure that returns one value for each manufacturer based on the majrity of the products categories.

Up until product category things are quite straight foward.

When categorizing the manufacturer, this is were things get messy.

 

I am not able to formulate a measure that does this calculation in a neat and fast way.

Knowing that my dataset counts more than 10 million records and increases every day, the filters on the pages are not a lot so the measure does exceed the limits.

 

Any help on how to write the measure effectively?

 

Thanks,

Sabine O.

Need Help

12 Replies

  • parry2k's avatar
    parry2k
    Super User
    4 years ago

    SabineOussi add a new column for product category and then a measure and that will do it

     

    Product Category Column = 
VAR __cost = 'Table'[Cost]
VAR __sales = 'Table'[Sales]
RETURN
SWITCH ( TRUE(),
    __sales >= 200 && __cost <= 5, "CAT1", 
    __sales >= 100 && __cost <= 10 , "CAT2", 
    __sales >= 50 && __cost <= 20, "CAT3", 
    "CAT4"
)

Manufacturer Category = 
VAR __m = MAX ( 'Table'[Manufacturer] )
VAR __table =  
SUMMARIZE ( 
    FILTER( 
        ALL ('Table' ), 
        'Table'[Manufacturer] = __m 
    ), 
    'Table'[Manufacturer], 
    [Product Category] ,  
    "@Cnt", COUNTROWS ('Table' ) )   
RETURN
MINX ( TOPN ( 1, __table, [@Cnt], DESC ), [Product Category] )

     

     

     

    ✨ Follow us on LinkedIn and  to our YouTube channel

     

    Check my latest video on Filters and Sparklines https://youtu.be/wmwcX8HvNxc

     

    I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

     

    ⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

    • SabineOussi's avatar
      SabineOussi
      Skilled Sharer
      4 years ago

      Thanks parry2k 

      The measure serves the purpose and results are well aligned.

       

      However when choosing a selection of more than one million records, the visual exceeds the number of available resources. My dataset has ~17M rows and expected to grow double by the end of the year.

       

      Any thoughts?

       

      Thanks,

      Sabine O.

    • SabineOussi's avatar
      SabineOussi
      Skilled Sharer
      4 years ago

      Hi parry2k
      I'm guessing by default it will take FIRST category result and I don't mind that.

       

      Thanks,

      Sabine O.

    • SabineOussi's avatar
      SabineOussi
      Skilled Sharer
      4 years ago

      parry2k Here you go

       

      ManufacturerProductSalesCost
      AA.11005
      AA.25010
      AA.3305
      AA.42015
      AA.55020
      AA.6805
      AA.71030
      BB.115015
      BB.22005
      BB.38010
      BB.46010
      BB.54015
      CC.15010
      CC.245045
      CC.32040
      CC.43010

       

      Thanks,
      Sabine O.

  • parry2k's avatar
    parry2k
    Super User
    4 years ago

    SabineOussi not surprised, do you have a separate table for the manufacturer, if not, we need to create one and that will improve the performance.

    • SabineOussi's avatar
      SabineOussi
      Skilled Sharer
      4 years ago

      parry2kHmm, wouldn't a separate table slow the performance?
      So I will have the main product table with an ID to the manufacturer and the manufacturer info in another table and they will be linked with ID?

      I'll give it a try and let you know.

       

      Thanks again,

      Sabine O.

  • parry2k's avatar
    parry2k
    Super User
    4 years ago

    SabineOussi no, star schema will improve the performance, right now it is scanning 1.7 m rows for each manufacturer, if we have a separate manufacturer dimension, it will be small and the scan time will be way little. These are the basics of data modeling and best practices for power bi. 

    • SabineOussi's avatar
      SabineOussi
      Skilled Sharer
      4 years ago

      parry2k You're absolutely right about that, my whole model is actually based on a star schema.

      For some reason I was thinking of something else.

       

      In all cases, I tried the measure on the manufacturer from the other table. Results are consistent however performance is still the same.

      Any other suggestion?

       

      Thanks,

      Sabine O.

  • v-luwang-msft's avatar
    v-luwang-msft
    Community Support
    4 years ago

    Hi  SabineOussi ，

    Has your problem been solved, if so, please consider Accept a correct reply as the solution or share your own solution to help others find it.

    Best Regards
    Lucien