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
debutant
Frequent Visitor

matrix: actual vs budet sales

Hello,

 

I am a beginner, I wish to compare actual vs budget sales but can't get it to work. My actual and budget numbers are both in the same table, along with the regions (one large excel sheet), could this be the problem ? 

Here is my table

 

debutant_4-1665105112722.png

 

 

My selections are :

 

debutant_1-1665104861081.png

debutant_3-1665104905953.png

 

Thank you for your help !

 

 

 

 

 

 

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@debutant , Having them in one sheet is not a problem. Not if there is a column type (Actual/budget)  and Value. then you need measures

 

Actual= calculate(Sum(Table[Value]), filter(Table, Table[Type] ="Actual"))

 

Budget= calculate(Sum(Table[Value]), filter(Table, Table[Type] ="Budget"))

 

 

Create a date table and other dimensions as per need

 

refer first 5-6 video of the series , I have discussed breaking of a large table

Power BI Tutorial Series for Beginners: https://www.youtube.com/watch?v=wvsAzTqSDVg&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb

 

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

View solution in original post

2 REPLIES 2
debutant
Frequent Visitor

@amitchandak 

thank you, this worked perfectly. 

 

I would like to make YTD comparison vs. prior year, I have written this measure but am stuck because it always shows the full year, I don't know how to make it so that it would only show the same period values as Current year sales, not the entire year. (surrent year woul be defined from a slicer)

 

PY Sales = CALCULATE(SUM('Data Base'[Sales in '000 EUR(Net GmbH)]), FILTER(ALL('Data Base'[Source]), 'Data Base'[Source] = 2021), FILTER(ALL('Data Base'[Scenario]), 'Data Base'[Scenario] = "Actual"))
 
If you have any idea how to do this would be great.
I have watched the first several videos, I am not sure if I should create a date or calendar table given that this is built into the excel sheet (in fench; year/quarter/month/day hierarchy) 
 
debutant_0-1665359933854.png

 

 

amitchandak
Super User
Super User

@debutant , Having them in one sheet is not a problem. Not if there is a column type (Actual/budget)  and Value. then you need measures

 

Actual= calculate(Sum(Table[Value]), filter(Table, Table[Type] ="Actual"))

 

Budget= calculate(Sum(Table[Value]), filter(Table, Table[Type] ="Budget"))

 

 

Create a date table and other dimensions as per need

 

refer first 5-6 video of the series , I have discussed breaking of a large table

Power BI Tutorial Series for Beginners: https://www.youtube.com/watch?v=wvsAzTqSDVg&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb

 

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

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.