Total average price change per category

[PVO3]

Hello all,

I'm trying to create a price change dashboard, but currently I'm a bit stuck. Seems like it's a 'total' problem.

See sample file

Expected result

Everything is fine, except when aggregating the products to, for instance Category. 

Category	Result	Expected result
Bright	100,00%	7,74%
Dark	900,00%	100,00%

Here I would like the average. So for example, the average of Dark, should be 300%+0% / 3 = 100%

But this has to be calculated for each selected date in 'Date' table. See the expected result link for the full overview.

Edit: corrected my expected result and added expected result details

[PVO3]

Anyone has a clue what direction I should go? Any feedback is greatly appreciated.

[davehus]

Hi @PVO3 ,

Average is an average of the column, I'd reckon you need to replicate your logic by maybe dividing the result by row count maybe?

Did I help you today? Please accept my solution and hit the Kudos button.

[PVO3]

Thanks for your reply @davehus. I tried a similar approach, but I couldn't make it work.

And unfortunatly, I still can't. As you can see all the result of other visuals is changing. Tried to work around this with something like this:

VAR _Count = CALCULATE(COUNTROWS(dimProduct),ALLSELECTED('Date'))

RETURN
IF(
    HASONEVALUE(dimProduct[ProductID]),
    _Result,
    DIVIDE(_Result,_Count)
)

But still doesn't give the result I'm looking for. I think I need to iterate over a virtual table and do an AVERAGEX over that. Also, perhapse I need to split the measure. A seperate measure for the graph (each date) and a seperate measure for the tables. I'll keep trying to make this work.