Filtered Average Value

[Anonymous]

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!

[v-robertq-msft · Accepted solution]

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.

[AlexisOlson]

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: