Subtract data from two visualizations?

[JonOlHei]

Hello there,

I'm pretty new to PowerBI, but love the possibilities it gives my company to get data. My issue now is that I have two different queries, where I want to subract the number from one visualisation from the other. Exampler under:

VISUALIZATION 1 (Query 1):

VISUALIZATION 2 (Query 2):

So: for example 2401 means year 24, week 01 and so on. What I now want is a calculation based on these two visualizations grouped by year/week (for example 2401 equal 92 (95-3)) - see example under, how I want this to look:

2401	2402	2403	2404	2405	2406	2407	2408	2409	2410	2411
92	260	235	229	134	232	201	203	251	267	32

I have tried to make a new measure, not managing. Any idea how I can solve this?

Thanks in advance 🙂 

[speedramps · Accepted solution]

Try this ...

First creatly a Calendar table with a list of all the weeks, from the fuirst weeks to the last week with no missing weeks.

let
    Source = List.Numbers(2401, 11),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Week"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Week", Int64.Type}})
in
    #"Changed Type"

Then create a one-to-many relationship from the Calendar tabel to Query1 and Query2

Create a new measure to calculate your answer 

Answer = SUM(Query1[Amount1]) - SUM(Query2[Amount2])

Create a new report page with new table visual

• Drag the Calendar week. Not the Query1 or Query 2 week.
• Drag Query1[Amount1]
• Drag Query1[Amount2]
• Drag your answer measure

You an learn more about Calendar table here

Calendar Table training

Thanks for the clear description of the problem with example data. I wish everyone did that!

Remember we are unpaid volunteers, and you have got free expert help which took a lot of effort,
This solution works and does exactly what you asked.
So please quickly click the [accept as solution] and the thumbs up button to leave kudos. 

One question per ticket please. If you need to change or extend your request then please raise a new ticket.

You will get a quicker response and each volunteer solver will get the kudos they deserve. Thank you ! 

If you quote @speedramps in your next tickets then I will then receive an automatic notification, and will be delighted to help you again.

Please now click the [accept as solution] and the thumbs up button.  Thank you.