DAX Help Perfects Stores by Salesman Calculation Help Required

[Anonymous]

I have a data in Below format : Salesman; Retailer;SKU;Suggested order qty ; Sold Qty.

Now i need to Show two things at salesman level

Distinct Outlet Suggested ( Which is simple to do and done )

Perfect Outlet covered ( where each Suggested SKU is sold with equal to or Higher qty then Suggested ) Need Help to calculate this.

Please help.

SalesMan	RTL	SKU	Suggested Order Qty	Qty Sold
S1	RTL1	SKU1	24	24
S1	RTL1	SKU2	28	28
S1	RTL2	SKU1	10	6
S1	RTL2	SKU3	30	6
S1	RTL1	SKU4	29	29
S1	RTL3	SKU2	23	23
S1	RTL3	SKU1	16	16
S1	RTL3	SKU3	26	26
S2	RTL1	SKU1	10	10
S2	RTL1	SKU2	14	19
S2	RTL2	SKU1	30	30
S2	RTL2	SKU3	28	28
S2	RTL1	SKU3	16	16
S2	RTL3	SKU2	12	5
S2	RTL3	SKU1	28	20
S2	RTL3	SKU3	23	18

[v-cazheng-msft · Accepted solution]

Hi @Anonymous,

You may try these two Measures.

count of retailers =
VAR res =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[RTL] ),
        ALLEXCEPT ( 'Table', 'Table'[SalesMan] )
    )
RETURN
    SUMX ( VALUES ( 'Table'[SalesMan] ), res )

Count of Retilers with Perfect Sales =
VAR NotPerfect =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[RTL] ),
        FILTER ( 'Table', 'Table'[Qty Sold] < 'Table'[Suggested Order Qty] )
    )
VAR PerfectSales = [count of retailers] - NotPerfect
RETURN
    PerfectSales

The result should look like this.

Also, attached the pbix file as reference.

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let me know. Thanks a lot!

Best Regards,

Community Support Team _ Caiyun

[Anonymous]

@v-cazheng-msft  Thanks For the solution !

It worked for Me.

Only point is thease measures are working fine at Small Data set, but my data is of very large and these measures are taking much time for calculation. is there any way to optimise the calculation by calculating some additional columns or by some other way..

[DimaMD]

Hi, @Anonymous If I understood your task correctly, you need to create a condition for displaying data in the matrix table.  
Red - not done.
Green - overfulfilled

[Anonymous]

@DimaMD  Thanks for relplying.

Actually that is not required.

required below calculation

Salesman    count of retailers     Count of Retilers with Perfect Sales

S1            3                                     2

S2            3                                     1

[DimaMD]

@Anonymous  
The first thing I did was add a column with perfect sales

The second wrote a measure with the calculation of ideal sales where anything greater than or equal to zero is individual sales. I think you were wrong with the desired result