Show Average % in Matrix Visualization using Dax Code

[Anonymous]

Hi,

I have a below requirements and looking for community assistance,

***Data Type for Year column is “Whole Number”

I am trying to get a result using below Dax code and I am getting an error.

DIVIDE(CALCULATE(AVERAGE('RAW Data'[Selling Price]),

                             ('RAW Data'[Year]=2018)), (CALCULATE(AVERAGE('RAW Data'[Selling Price]),('RAW Data'[Year]=2017))

Raw Data
Year	Product	Selling Price
2017	A	10
2017	A	20
2017	B	15
2018	B	30
2018	A	10
2019	A	20
2019	A	30

First Visulization Matrix Visulization
Product	Average Selling Price 2017	Average Selling Price 2018	Average Selling Price 2019
A	15	30	25
B	15	10

Final Result in Matrix Visulization
Product	2017	2018	2019
A	Divide %=Average Selling Price 2018/Average Selling Price 2017	Divide %=Average Selling Price 2019/Average Selling Price 2017	Divide %=Average Selling Price 2020/Average Selling Price 2019
B	Divide %=Average Selling Price 2018/Average Selling Price 2017	Divide %=Average Selling Price 2019/Average Selling Price 2017	Divide %=Average Selling Price 2020/Average Selling Price 2019

[RobbeVL]

Hi,

You made some mistakes in your final expeced result I think?

I cant seem to see any logic in that ? 

[Anonymous]

Hi Robbe,

Yes you are right.I have changed the Final Result Now.

Final Result in Matrix Visulization
Product	2017	2018	2019
A	Divide %=Average Selling Price 2018/Average Selling Price 2017	Divide %=Average Selling Price 2019/Average Selling Price 2018	Divide %=Average Selling Price 2020/Average Selling Price 2019
B	Divide %=Average Selling Price 2018/Average Selling Price 2017	Divide %=Average Selling Price 2019/Average Selling Price 2018	Divide %=Average Selling Price 2020/Average Selling Price 2019

[RobbeVL · Accepted solution]

Hi,

Easiest was to create 2 measures for this:

NxtYear = 
VAR
    NxtYear = (SELECTEDVALUE('Table'[Year]))+1
RETURN
CALCULATE(AVERAGE('Table'[Selling Price]); 'Table'[Year] = NxtYear)

Measure = DIVIDE([NxtYear];AVERAGE('Table'[Selling Price]))

Let me know if this helpend

[Anonymous]

Hi Robbe,

Thank so much it is working fine...you saved my lots of time.

Do you have any suggestion for below fromula? Final result should show  negative sign?

Measure = DIVIDE([NxtYear];AVERAGE('Table'[Selling Price]))

Measure

1.00%

-2.09%

Thanks