Anyway to put measures on both row and column in matrix?

[Penn]

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.

[Penn · Accepted solution]

Achieved this in SQL

[amitchandak]

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

[Penn]

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 %".