Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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???
Solved! Go to Solution.
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
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,
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.
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
84 | |
67 | |
60 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |