Measure returning majority category

[SabineOussi]

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.

[v-luwang-msft]

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

[SabineOussi]

@v-luwang-msft Unfortunately not, the performance is still poor.

Thanks,

Sabine O.

[parry2k]

@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]

@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.

[parry2k]

@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]

@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]

@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]

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.

[parry2k]

@SabineOussi sure. can you paste your data as a table.

[SabineOussi]

@parry2k Here you go

Manufacturer	Product	Sales	Cost
A	A.1	100	5
A	A.2	50	10
A	A.3	30	5
A	A.4	20	15
A	A.5	50	20
A	A.6	80	5
A	A.7	10	30
B	B.1	150	15
B	B.2	200	5
B	B.3	80	10
B	B.4	60	10
B	B.5	40	15
C	C.1	50	10
C	C.2	450	45
C	C.3	20	40
C	C.4	30	10

Thanks,
Sabine O.

[parry2k]

@SabineOussi what happens if there is a tie between categories?

[SabineOussi]

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

Thanks,

Sabine O.