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.
Dear all,
I need help.
I have a simple data model and have to calculate weighted distribution for products in stores per category.
WSD Calculation is very simple – I should sum StoreShare values only for stores Product has been sold at least ones.
Measure formulas I am using:
SV = CALCULATE(SUM(Sales[SalesVolume])+0) TotalStore = CALCULATE(SUM(Sales[SalesVolume]); ALLEXCEPT(Sales; Sales[Store])) TotalCategory = CALCULATE(SUM(Sales[SalesVolume]); ALLEXCEPT(Sales;Sales[Product]); ALL(Sales)) StoreShare = DIVIDE([TotalStore];[TotalCategory]) WSD = IF(ISBLANK([SV]); 0; [StoreShare])
All calculations goes well exept the most important one.
On my dashboard you can see what only Product4 should have 100% distribution, but I got 100% for every item.
Could you please help?
Thank you very much in advance
Solved! Go to Solution.
Hi @iRuso
Try this MEASURE for the CARD VISUAL
WSD = CALCULATE ( [Store Share], SUMMARIZE ( Sheet2, Sheet2[Store], "Share", [Store Share] ) )
Hi,
Your question is not clear. Share a dataset, explain the business question and show the expected result.
Hello,
This is link to my dataset and template
https://drive.google.com/file/d/1uy1fpaoC3eJkCeeGZSWT2QXeZmCzEpql/view?usp=sharing
Bussines case:
Weighted distribution for Retail.
We have 5 different products on sales in Retail Chain.
Total category sales calculation is very simple - CALCULATE(SUM([SalesVolume]); ALL())
Total Sales per Store also is easy - CALCULATE(SUM([SalesVolume]); ALLEXECPT(Table;Table[Store]))
The percent share of each store is calculated as Total Store Sales / Total Category Sales.
Now then one product is selected Weighted Distribution is Sum of Store Share only for stores where selected Product has been sold.
In my example only Product4 should have 100% Weighted distribution, but I got 100% for all items.
Could you please help me with correct formula?
Hi,
I still do nto understand your question. Please show me what exact % are you expecting in the card visual when each store is selected.
Hello,
For Product2 as showed above - Card Value should be 92.02.
This Sum of store share values.
Thank you in advance.
Hi @iRuso
Try this MEASURE for the CARD VISUAL
WSD = CALCULATE ( [Store Share], SUMMARIZE ( Sheet2, Sheet2[Store], "Share", [Store Share] ) )
Great !
It was easy. Thank you a lot!
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 |
---|---|
110 | |
99 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |