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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
aiplbonn
Regular Visitor

Calculate the difference of two fields from two different spreadsheets

Hey,
I am brand new to PowerBI and have a question.
I have an XLS file with two sheets, DATA and VOUCHER, which I load into Power BI.
Each of these sheets contains the year, month, partner and a sales value.
For DATA it is the turnover and for VOUCHER the voucher value.
In Power BI I have built something where I can select a partner for DATA, a month and year and then get the total turnover.
I have built the same for VOUCHER. I choose a partner, a month and a year and then I get the sum of the vouchers for this month.
Now of course I would like to have the real turnover, i.e. turnover minus voucher value.
How do I do this with Powerbi? I know how to do it with xls but I would like to do it with PowerBI.
Attached are screenshots of it.

I hope you can help me and my question is not too wrong.


2022-11-24 16_40_36-Mappe1 - Excel.png2022-11-24 16_40_56-.png2022-11-24 16_41_23-overview - Power BI Desktop.png

1 ACCEPTED SOLUTION
johnt75
Super User
Super User

Create a dimension table which has all your partners in it, like

Partners =
DISTINCT (
    UNION ( DISTINCT ( 'Data'[Partner] ), DISTINCT ( 'Voucher'[Partner] ) )
)

and create a one-to-many relationship from this new table to both Data and Voucher. Also create a date table, marked as a date table, and link that to both Data and Voucher.

You can then create a simple measure like

Total Revenue =
SUM ( 'Data'[Revenue] ) - SUM ( 'Voucher'[Voucher Revenue] )

and put that in visuals with columns from your Date and Partners tables.

View solution in original post

1 REPLY 1
johnt75
Super User
Super User

Create a dimension table which has all your partners in it, like

Partners =
DISTINCT (
    UNION ( DISTINCT ( 'Data'[Partner] ), DISTINCT ( 'Voucher'[Partner] ) )
)

and create a one-to-many relationship from this new table to both Data and Voucher. Also create a date table, marked as a date table, and link that to both Data and Voucher.

You can then create a simple measure like

Total Revenue =
SUM ( 'Data'[Revenue] ) - SUM ( 'Voucher'[Voucher Revenue] )

and put that in visuals with columns from your Date and Partners tables.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors