How to set up multi condition and then calculate the value

[tyan]

Hi. Thank you for helping me! I am a beginner still learning how to do powerbi...

so i have a database somthing like the table above.   I would like to calculate quanity % and price % by brand and department level.  The thing is I have to spill them by three conditions, >=100%,  between 99% ~ 70%, and less than 70%

The result I would like to show is provided in below table... I use NIKE as my example...  ( quanity %)

In my powerbi modeing, I have created a now column indicated which one is >=100%,  between 99% ~ 70%, and less than 70% this three condition.  But im still lost.. just dont show the below number that im looking for..

Someone please help~ 

[Icey · Accepted solution]

Hi @tyan ,

Please check if this is what you want:

Steps:

1. Enter data to create a Group table.

2. Create measures.

Quantity Measure 1 = 
SWITCH (
    MAX ( 'Group'[Order] ),
    1,
        CALCULATE (
            SUM ( 'Table'[Quantity] ),
            FILTER ( 'Table', 'Table'[Forecast vs Color] >= 1 )
        ),
    2,
        CALCULATE (
            SUM ( 'Table'[Quantity] ),
            FILTER (
                'Table',
                'Table'[Forecast vs Color] >= 0.7
                    && 'Table'[Forecast vs Color] <= 0.9
            )
        ),
    3,
        CALCULATE (
            SUM ( 'Table'[Quantity] ),
            FILTER ( 'Table', 'Table'[Forecast vs Color] < 0.7 )
        )
)

Quantity Measure 2 = 
SUMX ( VALUES ( 'Group'[Group] ), [Quantity Measure 1] )

3. Create Matrix visuals.

Best Regards,

Icey

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

[amitchandak]

@tyan , if you have already created a column with these values then you can use that in the matrix column to pivot the data. You can use count in values ?