Analyzing data in Matrix

[shanjaf]

Hi,

I am trying to analyze data in matrix, its a budget vs actual report. i have data in rows like below.

Type               Code                          Amount

Actual             ABC                              100,000

Budget            ABC                              500,000

Actual             XYZ                               50,000

Budget            XYZ                               600,000

Can anyone help me how can i put that in matrix, also show the difference between Budget and actual with variance percentage

i need report in following format

Code             Budget              Actual              Difference               Variance %

[ankitpatira]

@shanjaf In powerbi desktop click on Query Editor. Select your table -> select column -> Under Transform tab -> pivot column. Use that to convert rows in your Type column to columns and use Amount as values field. To calculate percentage create new measure of total amount using DAX. Then divide actual amount by total amount and change column type of that to percentage. If you're unsure with DAX let me know and I will post the code.

[Sean · Accepted solution]

@shanjaf I think this is what you are trying to do... Just create these 4 Measures and organize as in the picture

Let me know if it works.

[shanjaf]

I didn't try it, i think this will work, one more question. will that calculated field work in direct query?