Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I'd like to create a calculation within a matrix similar the following. Rows are Cohort, columns are Year and values in the matrix cells are based on the following logic: if Year == 2020 then average(Value 3) else sum(Value 1) / sum(Value 2) end
My data looks similar to the following.
Cohort | Year | Value 1 | Value 2 | Value 3 |
2016 | 2016 | 0.82143 | 0.30616 | 0.791628 |
2107 | 2107 | 0.547136 | 0.375904 | 0.088427 |
2018 | 2018 | 0.566549 | 0.846655 | 0.263815 |
2019 | 2019 | 0.091474 | 0.603061 | 0.371852 |
2016 | 2016 | 0.478472 | 0.327595 | 0.351641 |
2107 | 2107 | 0.602757 | 0.422631 | 0.773349 |
2018 | 2018 | 0.954778 | 0.722917 | 0.079683 |
2019 | 2019 | 0.020741 | 0.942776 | 0.908014 |
2016 | 2020 | 0.791773 | 0.678128 | 0.727963 |
2107 | 2020 | 0.83448 | 0.551655 | 0.873079 |
Solved! Go to Solution.
@Anonymous as my friend @Greg_Deckler recommend, it has to be measure, if you are use Year , you have to provide aggregation for year
if(MAX( year ) = 2020, average(value 3), sum(value 1) / sum(value 2))
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Sounds like you need a measure.
Measures seems to disagree as near as I can tell.. When I try entering a formula similar to: if(year = 2020, average(value 3), sum(value 1) / sum(value 2)) the formula bar wont even let me select the year column.
@Anonymous as my friend @Greg_Deckler recommend, it has to be measure, if you are use Year , you have to provide aggregation for year
if(MAX( year ) = 2020, average(value 3), sum(value 1) / sum(value 2))
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
User | Count |
---|---|
101 | |
90 | |
80 | |
71 | |
69 |
User | Count |
---|---|
114 | |
97 | |
97 | |
73 | |
72 |