Data aggregations on graphs, filtered by slicer that isn't on the graph

[Anonymous]

Hi guys,

First of all, sorry for my english. I will try to explain as better as I can.

I'm trying to create a dispersion graph. The elements shown have to be aggregated by some dimensions, but not for all. And it has to be able filter shown data by slicer.

I have this data:

And I want to create this graph:

If I hadn't to filter data by dimensions that aren't on graph, it would be easy.
When I get the data, I would do an "group by", I would select the option "no summarize" on axis metrics (I'm not sure if on english the option is called "no summarize" because I haven't installed the english version of Power BI) and it would be done, as you can see on pbix that I attached.

I tried to reproduce this logic with DAX.

As the column that I don't need is Date, I created a new table with the dates to slice them:

Calendar = VALUES(example[Date])

I created an measure to get the slicer selection, too:

Apply_filter = 
    VAR _min_date_sel = CALCULATE(MIN('Calendar'[Date]),ALLSELECTED('Calendar'[Date]))
    VAR _max_date_sel = CALCULATE(MAX('Calendar'[Date]),ALLSELECTED('Calendar'[Date]))
    VAR _date = MIN(example[Date])
    VAR _filter = IF(_date>=_min_date_sel,IF(_date<=_max_date_sel,1,0),0)
RETURN _filter

It works well. The problem appear when I create the summarize table, because the filter isn't applied.
I have created a new table with the aggregation:

example agg dax = 
SUMMARIZE (
    FILTER(example,example[Apply_filter]=1),
    example[Shop],
    example[Item],
    example[Color],
    "%_Units", AVERAGE(example[Units]),
    "%_Cost", AVERAGE(example[Cost])
)

But, when I show the data, I can see that the filter isn't applied. I waited an dynamic table by slicer, but not.
I waited for this result:

But I see this:

As you can see, the result is wrong. For Shop1, Pen, Blue, the correct value for Cost aggregation when I use the slicer is (0.5+2.2)/2=1.35, but the filter isn't applied and the result obtained is (0,5+2.2+0.8)/3=1,17.

I hope I have explained myself well, and you understand the problem. I attached the pbix and the excel data, for a better comprehension:

pbix and xlsx files 

Thanks in advance.

[lbendlin]

Thank you for providing the sample data. That helps a lot with proposing a potential solution.

Note that calculated tables (like calculated columns) are immutable. They cannot be influenced by filter choices.  You need to use measures instead. Power BI provides some explicit measures already, including averages.

See attached for a proposed solution using explicit measures.

[Anonymous]

Thank you very much for your answer, lbendlin. I provided as much as I could because, perhaps, I didn't know how to explain myself well, as is the case.

I haven't the problem on table, I have the problem on dispersion graph.

I just want aggregate the data by date, that is, I want the average by shop, item and color. If I use the explicit measures, I can choose from many options, "No summarize", "Sum", "Average", "Max", "Min", "Count"..., but I can't do this. If I choose "No summarize", I will show all lines from data. If I choose "Average", I will show the aggregations by Item_Color, but I won't see the shops on graph.

Maybe, I can solve this problem using a measure. I have to filter by selected Date and later group by Shop, Item and Color, but all my attempts to create it have failed.