Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi Power Bi Team,
I am Trying to work out this report but i have some difficulties to make this happen.
I have this table where this levels are displayed:
Fare Type | Route | Month | Company | Lead in fare |
Restrictive | XXX-YYY | Jun-13 | Company A | 185.00 |
Restrictive | XXX-YYY | Jun-13 | Company B | 169.00 |
what I need is the fare difference between A and B if:
- Fare type
- Route
- Month
are the same.
then I have to display this on a bar chart so the differential is displayed throughout the year.
Let me know if you have any ideas in mind.
thank you
Alberto
Solved! Go to Solution.
Hi @Anonymous
In query editor, please pay attention to the bold characters which you can use for your scenario.
Code in advanced editor,
let
Source = Excel.Workbook(File.Contents("C:\Users\maggiel\Desktop\case\6\6.20\Difference between.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Fare Type", type text}, {"Route", type text}, {"Month", type date}, {"Company", type text}, {"Lead in fare", Int64.Type}}),
#"Added Conditional Column" = Table.AddColumn(#"Changed Type", "CompanyA", each if Text.Contains([Company], "A") then [Lead in fare] else null),
#"Filled Down" = Table.FillDown(#"Added Conditional Column",{"CompanyA"})
in
#"Filled Down"
Then in Data Model View, create a calculated column
differentialA-B = [CompanyA]-[Lead in fare]
Best Regards
Maggie
Hi @Anonymous
In query editor, please pay attention to the bold characters which you can use for your scenario.
Code in advanced editor,
let
Source = Excel.Workbook(File.Contents("C:\Users\maggiel\Desktop\case\6\6.20\Difference between.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Fare Type", type text}, {"Route", type text}, {"Month", type date}, {"Company", type text}, {"Lead in fare", Int64.Type}}),
#"Added Conditional Column" = Table.AddColumn(#"Changed Type", "CompanyA", each if Text.Contains([Company], "A") then [Lead in fare] else null),
#"Filled Down" = Table.FillDown(#"Added Conditional Column",{"CompanyA"})
in
#"Filled Down"
Then in Data Model View, create a calculated column
differentialA-B = [CompanyA]-[Lead in fare]
Best Regards
Maggie
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
97 | |
75 | |
65 | |
53 |
User | Count |
---|---|
144 | |
103 | |
98 | |
85 | |
64 |