Forum Discussion
Table with measures difference (Month over Month)
Hi,
In my Power Bi file I have measures that I imported from Excel file (they were calculated in Power Pivot). My goal is to create such a table in Power Bi:
I don't know how to calculate the last two columns in this case. I don't have these differences calculated and there are more than 80 measures in my data, so it will be really hard to calculate all diffferences by hand. And then I won't be able to put measures as a column anyway.
The only solution that I may think of is to convert all my measures into a table. Where the first column is measures names and the second one is there values. But Im not sure how to do it. So maybe if someone knows how to do that, that could be helpful too!
Hi Anonymous ,
According to your description, it is possible to have the measure in column form, just turn on the Matrix option "Switch values to rows"
But I find it's hard to create one measure to calculate all previous month value, here's my solution.
1.Create a table like this:
2.Create measures:
Current M = SWITCH ( MAX ( 'Measure'[Measure] ), "Measure1", 'Table'[Measure1], "Measure2", 'Table'[Measure2] )Previous M = SWITCH ( MAX ( 'Measure'[Measure] ), "Measure1", 'Table'[PreviousM1], "Measure2", 'Table'[PreviousM2] )Diff = [Current M] - [Previous M]Diff% = DIVIDE ( [Diff], [Current M] )Put the created Measure column in Matrix Rows, all measures in Matrix Values, get the result:
I attach my sample below for your reference.
Best Regards,
Community Support Team _ kalyjIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
1 Reply
Hi Anonymous ,
According to your description, it is possible to have the measure in column form, just turn on the Matrix option "Switch values to rows"
But I find it's hard to create one measure to calculate all previous month value, here's my solution.
1.Create a table like this:
2.Create measures:
Current M = SWITCH ( MAX ( 'Measure'[Measure] ), "Measure1", 'Table'[Measure1], "Measure2", 'Table'[Measure2] )Previous M = SWITCH ( MAX ( 'Measure'[Measure] ), "Measure1", 'Table'[PreviousM1], "Measure2", 'Table'[PreviousM2] )Diff = [Current M] - [Previous M]Diff% = DIVIDE ( [Diff], [Current M] )Put the created Measure column in Matrix Rows, all measures in Matrix Values, get the result:
I attach my sample below for your reference.
Best Regards,
Community Support Team _ kalyjIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.