topic Re: DAX Help Perfects Stores by Salesman Calculation Help Required in DAX Commands and Tips

DAX Help Perfects Stores by Salesman Calculation Help Required

Anonymous — Fri, 17 Jun 2022 07:29:31 GMT

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

Re: DAX Help Perfects Stores by Salesman Calculation Help Required

DimaMD — Fri, 17 Jun 2022 07:56:19 GMT

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

Re: DAX Help Perfects Stores by Salesman Calculation Help Required

Anonymous — Fri, 17 Jun 2022 08:36:29 GMT

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

Re: DAX Help Perfects Stores by Salesman Calculation Help Required

DimaMD — Fri, 17 Jun 2022 09:18:04 GMT

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

Re: DAX Help Perfects Stores by Salesman Calculation Help Required

v-cazheng-msft — Tue, 21 Jun 2022 03:19:11 GMT

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

Re: DAX Help Perfects Stores by Salesman Calculation Help Required

Anonymous — Fri, 24 Jun 2022 05:12:29 GMT

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