Metric to use on a List(table?) Chart.

[bolabuga]

Hello, i have the sample table on the image.

I did the following metric to use on a grouped bar chart:

INDICATOR 2016 % = 
	VAR MONEYTYPE2015 = CALCULATE(SUM('SAMPLE'[MONEY]);FILTER('SAMPLE';'SAMPLE'[TYPE] <> 2 && 'SAMPLE'[YEAR]=2015))
	+ 
	CALCULATE(SUM('SAMPLE'[MONEY]);FILTER('SAMPLE';'SAMPLE'[TYPE] <> 0 && 'SAMPLE'[YEAR]=2015)) 
	VAR MONEYTYPE2016 = CALCULATE(SUM('SAMPLE'[MONEY]);FILTER('SAMPLE';'SAMPLE'[TYPE] <> 2 && 'SAMPLE'[YEAR]=2016))
	+ 
	CALCULATE(SUM('SAMPLE'[MONEY]);FILTER('SAMPLE';'SAMPLE'[TYPE] <> 0 && 'SAMPLE'[YEAR]=2016)) 
	RETURN
	DIVIDE(MONEYTYPE2016;MONEYTYPE2015)-1

I had to make another 2 metrics to mount a list showing summarized values used on the calculations in the "indicator 2016 %"

MONEY TYPE 2015 = CALCULATE(SUM('SAMPLE'[MONEY]);FILTER('SAMPLE';'SAMPLE'[TYPE] <> 2 && 'SAMPLE'[YEAR]=2015)) + CALCULATE(SUM('SAMPLE'[MONEY]);FILTER('SAMPLE';'SAMPLE'[TYPE] <> 0 && 'SAMPLE'[YEAR]=2015))

MONEY TYPE 2016 = CALCULATE(SUM('SAMPLE'[MONEY]);FILTER('SAMPLE';'SAMPLE'[TYPE] <> 2 && 'SAMPLE'[YEAR]=2016)) + CALCULATE(SUM('SAMPLE'[MONEY]);FILTER('SAMPLE';'SAMPLE'[TYPE] <> 0 && 'SAMPLE'[YEAR]=2016))

My doubt is, i think theres a better way to mount the list on the screenshot, without having to make the 2 newer metrics (money type 2015 e money type 2016). I tried use "summarizecolumns", but im not grasping how to make it work. 

Then, the question: How to make this scenario work with fewer Metrics???

[v-sihou-msft]

@bolabuga

Based on your source table, it's better to use a matrix instead of a table visual to render the data.

In your scenario, your calculation need to use Year 2015 calculation divide by Year 2016 calculation. We can add a column in your table which returns previous year data.

Previous Year Value = CALCULATE(SUM(Table3[Value]),FILTER(Table3,Table3[Year]=EARLIER(Table3[Year])-1 && Table3[Month]=EARLIER(Table3[Month])))

Then we can create a "Current Year/Previous Year" measure and add it into the matrix.

Current / Previous = IF(CALCULATE(SUM(Table3[Previous Year Value]))=BLANK(),0,CALCULATE(SUM(Table3[Value]))/CALCULATE(SUM(Table3[Previous Year Value])))

'

Regards,

[bolabuga]

simon hou-msft thks for trying to help :).

The idea seens very good, but its not working because the calculated column repeat the sum for every client each mont.

showing the sample data.

CLIENT	MONTH	YEAR	MONEY	TYPE
3	1	2015	50	0
7	1	2015	97	1
5	1	2015	25	2
3	1	2016	337	0
7	1	2016	277	1
5	1	2016	555	2
3	2	2015	150	0
7	2	2015	137	1
5	2	2015	110	2
3	2	2016	127	0
7	2	2016	156	1
5	2	2016	322	2
3	3	2015	120	0
7	3	2015	667	1
5	3	2015	160	2
3	3	2016	44	0
7	3	2016	145	1
5	3	2016	80	2

The result showing the replicated results on the calculated column.

It should have a way to do this without repeating the sum for every different client in the calculated column.

[v-sihou-msft · Accepted solution]

@bolabuga

In your scenario, you also have client column in your table, you should also add "Table[Client]=EARLIER(Table[Client])" in your filter.

Previous Year Value = CALCULATE(SUM(Table[Value]),FILTER(Table,Table[Year]=EARLIER(Table[Year])-1 && Table[Month]=EARLIER(Table[Month]) && Table[Client]=EARLIER(Table[Client])))

Since you need to do this comparison in matrix, it will generate columns dynamically and always aggregate on Values cell, I can't find a better way using a "previous year data" column.

Regards,

Simon Hou

[bolabuga]

Worked Simon_Hou-MSFT, really thks, it is a much better way to do, because i can use just one metric across all years. Really cool. thkk again