Dynamically changing Income Statement based on the slicer selection

[BRIANCC]

Hello experts,

I've been assigned to build a dynamically changing Income statement on Power BI, but I have absolutely zero clue how to make this happen.

Just like the image below, assuming that there's 1 Revenue item and 3 Cost items,

① The report users might select through the slicers whether the Cost 1~3 should be 'Cost of Sales' or 'SG&A' cost.

② Based on the user's selection, the income statement table on the right should change accordingly.

For example, if the user selected Cost 1 as COS, and Cost 2,3 as SG&A, then the table should show these items in the following order and calculate Gross Profit and Operating Profit accordingly.

  : Revenue → COS (Cost 1) → Gross Profit (*calculate Revenue - COS) → SG&A (Cost 2, 3) → Operating Profit (*calculate Gross Profit - SG&A)

 

Any kind of your help/suggestion would be greatly appreciated.

Thank you experts, in advance.

[BRIANCC · Accepted solution]

Hi @Anonymous , thanks for your kind support.

Do you have any additional idea on how I can put calculations (i.e. Gross profit and Operating Profit) in the middle of the table? Say, when only Cost 1 is selected as COS, the table may show
Revenue  10,000
Cost 1 (COS) 1,000
-Gross Profit = 10,000 - 1,000 = 9,000
Cost 2 (SG&A) 1,000
Cost 3 (SG&A) 1,000
-Operating Profit = Gross Profit - SG&A = 9,000 - 2,000 = 7,000

Thanks!

[Anonymous]

Hi @BRIANCC ,

Below is my table1:

Below is my table2:

You can create a relationship between two tables:

The final output is shown in the following figure:

Best Regards,

Xianda Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[BRIANCC · Accepted solution]

Hi @Anonymous , thanks for your kind support.

Do you have any additional idea on how I can put calculations (i.e. Gross profit and Operating Profit) in the middle of the table? Say, when only Cost 1 is selected as COS, the table may show
Revenue  10,000
Cost 1 (COS) 1,000
-Gross Profit = 10,000 - 1,000 = 9,000
Cost 2 (SG&A) 1,000
Cost 3 (SG&A) 1,000
-Operating Profit = Gross Profit - SG&A = 9,000 - 2,000 = 7,000

Thanks!

[Anonymous · Accepted solution]

Hi @BRIANCC ,

Putting Gross Profit and Operating Profit in the middle of a table is a bit unachievable, but you can create two measures and then put the metrics into a card visual object that will dynamically display your Gross Profit and Operating Profit.

The following DAX might work for you:

Operating Profit = [Gross Profit] - 1000*CALCULATE(COUNTROWS('Table (2)'),FILTER('Table (2)','Table (2)'[Class] = "SG&A"))

Gross Profit = 10000 - 1000*CALCULATE(COUNTROWS('Table (2)'),FILTER('Table (2)','Table (2)'[Class] = "COS"))

The final output is shown in the following figure:

Best Regards,

Xianda Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.