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.
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!
Solved! Go to Solution.
@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,
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,
Ignoring the subtypes, the solution would calculate as follows:
Value = (50*4.80 + 20*5.00 + 40*3.00)/(50 + 20 + 40) = ~4,18
@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,
Thank you very much! This is exactly what I was looking for. However, I would call your first measure "Sum of USD/lb" and the second one "Sum of Pieces Sold". This makes more sense and is easier to understand for others who might stumble upon this thread.
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 |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |