Forum Discussion
Calculated Measures as Rows and Columns in Matrix
Hello Community -
I am looking for some help with presenting Multiple Measures within a matrix visual. I have the following measures:
Sales = SUMX(Orders, Dollars_Sale)
COGS = SUMX(Orders, Order_cost)
$ Margin = Sales - COGS
I then have branched off of those measure to calculate my MTD, QTD, YTD measures. What I am looking to do is create a matrix visual that will show my 3 measures (Sales, COGS, $ Magin) as rows, and then values be the MTD, QTD, YTD measures.
I know that you are able to switch the values to rows, but I do not want my To Date measures to show as rows, I want those to be the columns in the matrix.
Is there a way to create this type of Measure? Below is what I would want the output to look like:
| Measure | MTD | QTD | YTD |
| Sales | 1000 | 1650 | 2900 |
| COGS | 600 | 1100 | 2275 |
| $ Margin | 400 | 550 | 625 |
Thank you in advance for any advice and input!
Ryan
Take a look at the Calculation Groups functionality. I belive it will accomlish what you are looking for. Here are some videos discussing the topic and showing how to create them:
https://www.youtube.com/watch?v=vlnx7QUVYME&ab_channel=GuyinaCubehttps://www.sqlbi.com/blog/marco/2020/07/15/creating-calculation-groups-in-power-bi-desktop/
3 Replies
Take a look at the Calculation Groups functionality. I belive it will accomlish what you are looking for. Here are some videos discussing the topic and showing how to create them:
https://www.youtube.com/watch?v=vlnx7QUVYME&ab_channel=GuyinaCubehttps://www.sqlbi.com/blog/marco/2020/07/15/creating-calculation-groups-in-power-bi-desktop/
jdbuchanan71 this works great. But I am having one issue with it. When I want to look at MTD LY my calculation group does not calculate that properly. I seem to always get 1 of 2 outputs. It either gives me the Current MTD instead of LY. Or it will give me LY but for the entire month,.
Here is the measure I am using in my Calculation group to try to get MTD LY for a partial month, but this gives me just Current MTD
VAR _MTD = TOTALMTD( SELECTEDMEASURE(), Rolling_Calendar[Date] ) RETURN CALCULATE( _MTD, SAMEPERIODLASTYEAR(Rolling_Calendar[Date] ) )
Is there a way to calculate the parital MTD LY within calculation groups? I want to be able to see my Sales for October 2019 but only through October 19th.
Thanks for all the insight!
There are some tricks you can use to stop future dates from coming into PY calculations. Take a look at this article that goes through the steps of how to handle them.
https://www.sqlbi.com/articles/hiding-future-dates-for-calculations-in-dax/
