The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hi Everyone,
I'm finding it difficult to find the % between two specific rows.
As shown in the sample data, I have Revenue and Total Operating Income and I want to see the running % (increase/decrease) across the years as my desired result.
I do not have year in my data as rows its listed in cloumns, therfore cannot use the max or index logic.
Fiscal Year 2015 Actuals | Fiscal Year 2015 Plan | Fiscal Year 2017 | Fiscal Year 2018 | |
Total Revenue | $ 258,376.00 | $ 144,362.00 | $ 288,731.00 | $ 376,504.00 |
Total Operating Income | $ 98,705.00 | $ 78,083.00 | $ 53,640.00 | $ 51,141.00 |
Desired result in power bi % | 38% | 54% | 19% | 14% |
Thank you!
Solved! Go to Solution.
Hi,
Do you want this? Download the file from here.
Hi,
Would you be OK with an output where all years are listed down one column and there will be 2 additional columns - Revenue and Operating Income. We can carry out whatever calculations we want.
So nothing will change in your input - we will just apply some transformations to get the output outlined above.
Let me know?
I have my data in that formast as well - tried doing that....
However my years are not in standard format like 2015, 2016, 2017 and so on - in that instance we could use the -1 dax formula (this year vs previous year). My year format is FY16 Actual, FY16 Plan, FY16 Forecast and so on....
Hi,
From FY16, extract 16 by using split column. Build a date from this and then use all DAX formulas.
If below is my format How could I do it using DAX?
Hi,
Do you want this? Download the file from here.
In PowerBI you can do it in Query EDITOR with a series of PIVOT and UNPIVOTS
let Source = Excel.CurrentWorkbook(){[Name="TableName"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"ITEM", type text}, {"Fiscal Year 2015 Actuals", type number}, {"Fiscal Year 2015 Plan", type number}, {"Fiscal Year 2017", type number}, {"Fiscal Year 2018 ", type number}}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"ITEM"}, "Attribute", "Value"), #"Pivoted Column" = Table.Pivot(#"Unpivoted Columns", List.Distinct(#"Unpivoted Columns"[ITEM]), "ITEM", "Value", List.Sum), #"Added Custom" = Table.AddColumn(#"Pivoted Column", "Desired Result", each ([Total Operating Income]/[Total Revenue])*100), #"Unpivoted Columns1" = Table.UnpivotOtherColumns(#"Added Custom", {"Attribute"}, "ITEMS", "Value"), #"Pivoted Column1" = Table.Pivot(#"Unpivoted Columns1", List.Distinct(#"Unpivoted Columns1"[Attribute]), "Attribute", "Value", List.Sum) in #"Pivoted Column1"
Thank you for the prompt reply!
Could you please guide me with the steps to do the above?
Hi @Namratha
See the attached Excel File here
You can follow the steps from the QUERY Editor
Hello,
My excel file has only two rows : Total Revenue and Total Operating Income
- Desired result (i.e. Operating Income/Total Revenue) is the output I want to see in Powerbi as my result.
Firstly I load my excel sheet in power bi and this how it looks like:
- next while I go to my query editor this is how it appears:
Could you please guide me with instructions after this step above?
I prepared this pbix file for you.
You can follow the steps from the APPLIED steps section
Thank you I just got the fix I was missing....
Can we do this by adding a measure - so that way I can use multiple measures to see % for multiple rows/different row combinations in my dataset?
- my data is really huge and it is not only 2 standard comparisons that I require
ex: income vs revenue
expense vs revenue
expense vs income etc...
Also the calculated values should reflect in % format
If your Data is in proper Tabular Format.. you can easily do lot of analysis using Pivot tables, slicers and DAX formulas
Yes my data is in the format that I have shared in the screenshot above (excel spreadsheet).
- There are addiional line items like total expenses , total allocation , total net profit etc....
Would need help on dax to calucate and create measures for the same.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
141 | |
109 | |
108 | |
76 | |
63 |
User | Count |
---|---|
273 | |
129 | |
123 | |
101 | |
91 |