Calculating % between specific ROWS

[Namratha]

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!

[Ashish_Mathur]

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?

[Namratha]

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....

[Ashish_Mathur]

Hi,

From FY16, extract 16 by using split column.  Build a date from this and then use all DAX formulas.

[Namratha]

If below is my format How could I do it using DAX?

[Ashish_Mathur · Accepted solution]

Hi,

Do you want this?  Download the file from here.

[Zubair_Muhammad]

@Namratha

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"

[Namratha]

Thank you for the prompt reply!

Could you please guide me with the steps to do the above?

[Zubair_Muhammad]

Hi @Namratha

See the attached Excel File here

You can follow the steps from the QUERY Editor

[Namratha]

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?

[Zubair_Muhammad]

@Namratha

I prepared this pbix file for you.

Click here

You can follow the steps from the APPLIED steps section

[Namratha]

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

[Zubair_Muhammad]

@Namratha

If your Data is in proper Tabular Format.. you can easily do lot of analysis using Pivot tables, slicers and DAX formulas

[Namratha]

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.