Forum Discussion
Dynamically changing Income Statement based on the slicer selection
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.
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!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.
3 Replies
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.
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!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.