Forum Discussion
DAX Help Perfects Stores by Salesman Calculation Help Required
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 |
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 PerfectSalesThe 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
5 Replies
DimaMDSolution Sage
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 - overfulfilledDimaMD 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
v-cazheng-msftCommunity Support
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 PerfectSalesThe 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
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..