Forum Discussion
Filtered Average Value
Hi! I am still figuring my way around Power BI therefore I am facing some difficulties with the following.
I have this Table "Requirements":
| Tool | Account | Requirements Level 1 | Requirements Level 2 | Value |
| Spoon | Sales | A | AA | 1 |
| Spoon | Sales | A | AB | 2 |
| Fork | Sales | B | BA | 3 |
| Fork | Sales | B | BB | 4 |
| Fork | Sales | C | CA | 5 |
| Knife | Sales | C | CB | 5 |
| Knife | Sales | C | CC | 6 |
| Knife | Sales | C | CD | 7 |
What I am trying to achieve is described in the attached picture (thought it would be much easier if I offered a visual, forgive my drawing skills).
I need to show the average sale value for each Requirement level 1 (A, B, C) with the tool as the legend.
I am pretty sure I need to use a DAX formula but maybe I need to create a new column? Please help I am rather confused!
Thank you so much in advance!
Hi, Anonymous
According to your description, you want to create a measure to get a column chart as you expected, you can try this measure:
This is my test data, I added some data based on yours to display value for each column in the chart, like this:
Average = CALCULATE( AVERAGE('Table'[Value]), FILTER( ALLSELECTED('Table'), [Tool]=MAX('Table'[Tool])&& [Requirements Level 1]=MAX('Table'[Requirements Level 1] )))Then I create a Clustered column chart and placed it like this:
And you can get what you want.
You can download my test pbix file here
Best Regards,
Community Support Team _Robert Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
You can do this without writing any DAX.
Create a clustered column chart and put Requirements Level 1 in the Axis filed, Tool in the Legend field, and Value in the Values field (and choose Average from the drop-down where you can choose what aggregation to use).
For your data:
Hi, Anonymous
According to your description, you want to create a measure to get a column chart as you expected, you can try this measure:
This is my test data, I added some data based on yours to display value for each column in the chart, like this:
Average = CALCULATE( AVERAGE('Table'[Value]), FILTER( ALLSELECTED('Table'), [Tool]=MAX('Table'[Tool])&& [Requirements Level 1]=MAX('Table'[Requirements Level 1] )))Then I create a Clustered column chart and placed it like this:
And you can get what you want.
You can download my test pbix file here
Best Regards,
Community Support Team _Robert Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
