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.
Hi All,
I have a dataset looks like this.
Item | Division | Sub-division | Date | Type | Amount |
A001 | Book | Travel Book | 25/10/2019 | Revenue | 10 |
A001 | Book | Travel Book | 25/10/2019 | Cost | -5 |
A002 | Book | Travel Book | 4/09/2019 | Revenue | 15 |
A002 | Book | Travel Book | 4/09/2019 | Cost | -5 |
A003 | Game | PS4 Game | 2/10/2019 | Revenue | 5 |
A004 | Game | PS4 Game | 3/10/2019 | Cost | -4 |
And I have a strict requirement to convert this dataset into a matrix like below, which inevitably means I need to put measures on both the row and column. Is there any addon in market place that can achieve this?
I understand that it is an easy task in Excel, but anyway we can do this in Power BI? Thanks.
Solved! Go to Solution.
Achieved this in SQL
If you have a dataset where type is revenue and cost etc. Then create a measure for this month, last month and MOM. And use Matrix. In a matrix their symbol, which can drill you till last level.
There is option to show values on row. But looking at your structure you do not need that.
Formula's can be
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date Filer])) Lst month Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date Filer],-1,MONTH))))
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.
My Recent Blog - https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601
Hi amitchandak,
Thanks for your post, however this doesn't exactly solve my question. Noted that in my dataset there are only two types "Revenue" and "Cost" and I need to report more types in the row like "Margin" and "Margin %".
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 |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |