Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
shanjaf
New Member

Analyzing data in Matrix

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 %

 

1 ACCEPTED SOLUTION
Sean
Community Champion
Community Champion

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

Matrix - Budget.png

Let me know if it works.

View solution in original post

3 REPLIES 3
ankitpatira
Community Champion
Community Champion

@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
Community Champion
Community Champion

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

Matrix - Budget.png

Let me know if it works.

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

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.