Forum Discussion
Weighted Avg Aggregation in DAX
Hi,
I have already looked around a bit, but didn't find any solution to my problem.
I have two columns in my table ("Apples Sold" and "Profit per Apple"). In addition I have numerous types of apples which are reflected in the rows of the table. My goal is now to create a simple vertical bar chart that shows per default the total amount of apples sold and another chart that shows the average profit per apple. Creating the first chart is simple enough using the "Sum" function in the variable selction.
However, getting the average profit per apple, which is defined as the weighted average of all the types of apples, seems impossible. I have experimented with custom measures, but did not reach a satisfactory conclusion. What further complicates the task is that I have a slicer in my report that allows users to select the apple type they want to see. Naturally, the weighted average should show the correct value when a type is selected as well as when none is selected.
Any help is highly appreciated!
MA,
Create the following measures in your table and create a bar chart as shown in the following screenshot.
Sum of Pieces Sold = CALCULATE(SUMX(Table2,Table2[Pieces Sold]*Table2[USD/lb]),FILTER(Table2,Table2[Type]="Red" ||Table2[Type]="Yellow"||Table2[Type]="Green"))
Sum of USD/lb = CALCULATE(SUM(Table2[Pieces Sold]),FILTER(Table2,Table2[Type]="Red" ||Table2[Type]="Yellow"||Table2[Type]="Green"))
Average = [Sum of Pieces Sold]/[Sum of USD/lb]
Measure = IF(ISFILTERED(Table2[Type]),
SUM(Table2[USD/lb]),
[Average]
)
Regards,
6 Replies
maybe you give some more visual details what you want
weighted average sounds like SUMX calc but I am unsure in which form exactly you need the result
Thanks for the quick reply, here are some visuals:
Let's assume this data:the report looks similar to this:
The graphs work fine when a type is selected, but there is no appropriate way to show the total USD/bbl value when no type is selected. Furthermore, I have subgroups to types (red juicy, red sweet to red), but I already accepted that I probably have to work without them, as they would somehow need to be excluded in a weighted average calculation.
MA,
What value do you want to display for USD/lb when no type is selected in slicer? When you choose "Average" for USD/lb in the chart, does it return your expected result?
Regards,
