Weighted average, totals & subtotals in visualizations

[PBI_Michael]

Hi, I have data sheets with thousands of rows, with different types products divided into categories and multiple sub-categories, but the products have weights, I need to create different types of charts with this data but all the charts have to account for the weights and include the ability to drill down all subcategories. I can easily set up the charts I need but they will use the nonweighted average and I need all my measures and data in the visualizations to be affected by the weights

Below I include a sample of the data and an example of the 100% stacked charts I need:

Thanks in advance.

[Ashish_Mathur]

Hi @PBI_Michael,

I would create 3 seperate columns in the bae data - one each for the measure.  So "Weighted measure1" would be:

=[ProductWeight]*[Measure1]

Do the same for the other 2 measures as well.

[v-ljerr-msft]

Hi @PBI_Michael,

If I understand you correctly, you should be able to use the formulas below to create new measures to calculate weighted average for measure1, measure2, measure3, then show the new created measures instead on your report. 

New Measure 1 = SUMX ( 'Table1', 'Table1'[ProductWeight] * 'Table1'[measure1] )

New Measure 2 = SUMX ( 'Table1', 'Table1'[ProductWeight] * 'Table1'[measure2] )

New Measure 3 = SUMX ( 'Table1', 'Table1'[ProductWeight] * 'Table1'[measure3] )

Regards

[PBI_Michael]

 Hi, I might be getting this wrong, but if I only use the SUMX function to get the weighted measures then when I need to average the new measures by category or subcategory I'll get a wrong number, for example, subcategory1 would have 295 products and so when averaging New Measure 1 it will be divided by 295, but accounting for the weights in reality Subcategory1 has 980 products, so the correct average of New Measure 1 by Subcategory1 Should be (New Measure 1) / 980 

Also, I should have mentioned that the weights and categories are in a separate table than the Measures so should I merge them into a new table that has all measures and their corresponding weights by product? 

Finally, what about the Averagex Function, I read about it but I'm still confused about this and don't understand it completely.

Thanks for the reply

[PBI_Michael · Accepted solution]

Hi, I did something that seems to work, but I'm unsure if this is the best solution to the problem, I created 3 calculated columns: 

Weighted column1 = [ProductWeight]*[Measure1] 

Weighted column2 = [ProductWeight]*[Measure2] 

Weighted column3 = [ProductWeight]*[Measure3] 

Then created a calculated field to get the sum of each column using regular sum function:

Total of measure1 = sum ('Table1'[measure1] )

Total of measure2 = sum ('Table1'[measure2] )

Total of measure3 = sum ('Table1'[measure3] )

Calculated the Sumx of weights: 

Total weight = SUMX ('Table1', 'Table1'[ProductWeight] * 'Table1'[weight] )

Finally Calculated the Average of each measure:

Avg measure1 = Total of measure1 / Total weight 

Avg measure2 = Total of measure2 / Total weight 

Avg measure3 = Total of measure3 / Total weight 

I had to do it this way because if I only create the weighted columns or the Sumx for each measure the charts will show a wrong value when filtering for subcategories like this:

 also, the tooltip will show a wrong value.

Thanks for the help and I'd really appreciate it if some expert could tell me if my solution is wrong or if it can be done in a much simpler way, I'm averaging percentages, so the tooltip has to show the exact same value as the 100%  stacked bar, also the values have to be weighted average has to be adjusted by category and subcategory, I saw some youtube videos where they used the calculate function and Averagex but I couldn't make it work.

Finally, I have a treemap in the report that filters by category, but if I filter using the treemap I get a wrong value in the tooltip, but if I filter with a slicer I get the right value.

Thanks