Forum Discussion

iRuso's avatar
iRuso
Regular Visitor
8 years ago
Solved

Weighted Distribution calculation

 

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

 

Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    Hi iRuso

     

    Try this MEASURE for the CARD VISUAL

     

    WSD =
CALCULATE (
    [Store Share],
    SUMMARIZE ( Sheet2, Sheet2[Store], "Share", [Store Share] )
)

6 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    8 years ago

    Hi,

     

    Your question is not clear.  Share a dataset, explain the business question and show the expected result.

    • iRuso's avatar
      iRuso
      Regular Visitor
      8 years ago

      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?  

       

       

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        8 years ago

        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.