The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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 🙂
Solved! Go to 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
You an learn more about Calendar table here
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.
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
You an learn more about Calendar table here
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.