Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello to all,
I have come to my personal limit with DAX...
I am trying get the cost of opporinity from each store. By cost of oppotunity I meen the stores that dont have disponibility of one product, look for the average sales of this product from the other stores and sum this as "Lost Sales".
It sounds simple but I dont have any clue how to formulate this in DAX...
Example:
I hope you can help me
Solved! Go to Solution.
Hi @Anonymous,
In your scenario, you can create a measure like below:
Lost Sales = IF(MIN('storetable'[Disponibility])=0,
CALCULATE(SUM(storetable[Sales value]),ALLSELECTED( storetable ), VALUES( storetable[Product] ))/CALCULATE(COUNT(storetable[Sales value]),ALLSELECTED( storetable ), VALUES( storetable[Product] )),BLANK())
Then you can place "Store" field in a slicer visual, check different values to filter table data.
Best Regards,
Qiuyun Yu
Hi @Anonymous,
In your scenario, you can create a measure like below:
Lost Sales = IF(MIN('storetable'[Disponibility])=0,
CALCULATE(SUM(storetable[Sales value]),ALLSELECTED( storetable ), VALUES( storetable[Product] ))/CALCULATE(COUNT(storetable[Sales value]),ALLSELECTED( storetable ), VALUES( storetable[Product] )),BLANK())
Then you can place "Store" field in a slicer visual, check different values to filter table data.
Best Regards,
Qiuyun Yu
I think you are a trying to do the something like the following:
measure=if(hasonevalue(storetable'[store]), divide(calculate(sum('table'[salesColumn]), filter(all('storetable'), 'storetable'[store] <> values('storetable'[store]))), calculate(sum('table'[ordercount]), filter(all('storetable'), 'storetable'[store] <> values('storetable'[store]) ))), blank())
you could also use a variable to store the filtered table to make the code a bit cleaner, but that would be a little more complicated.
Hope that helps
Proud to be a Super User!
Hi richbenmintz,
Thank you very much for the fast repley.
It looks like its working but only if I select a single store with a slicer, otherwise I cant view the measure...
Is there a way to view it for all the stores at the same time?
correct the measure will only work if there is a single store in the filter context. if you add store to rows or columns then you will be able to use multiple stores in your slicer
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
94 | |
76 | |
62 | |
50 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |