topic Re: Dax Measure for P&L vertical Analysis in DAX Commands and Tips

Dax Measure for P&L vertical Analysis

Nauman — Tue, 27 Jun 2023 14:16:24 GMT

Dear Experts,

I am seeking assistance in creating a measure to calculate the percentage of Sales for all the amounts in the "Sub-Type" column of my profit and loss table. Unfortunately, I have not been able to obtain the desired result so far. Initially, I attempted to calculate the total sales and then divide each line item by this value using a measure called "Vertical Analysis." However, this approach only provides the percentage for the Sales item, leaving the other line items such as COGS and other OpEx items blank.

I would greatly appreciate any help you can provide to address this issue.

Thank you in advance.

Ashish_Mathur

Total Sales =

CALCULATE(

SUM(PL_Unpivot[Amount]),'PL-Category'[Category]= "Sales"

Vertical Analysis = ([Total Sales] / CALCULATE([Total Sales],ALL(PL_Unpivot[Sub-Type])))

Re: Dax Measure for P&L vertical Analysis

eliasayyy — Tue, 27 Jun 2023 14:34:58 GMT

hello your measure total sales is only calculating catehory = sales so anything ese will return 0 unless its sales so in vertical analysis,
instaed of CALCULATE([Total Sales],ALL(PL_Unpivot[Sub-Type])))
use CALCULATE(SUM([amount]),ALL(PL_Unpivot[Sub-Type])))

Re: Dax Measure for P&L vertical Analysis

Nauman — Tue, 27 Jun 2023 16:25:35 GMT

The measure is still yielding the same result. However, the measure provided below is functioning to some extent by dividing each category's total by its corresponding line item. However, what I actually want is to divide all the line items by the total sales. For instance, if we have a total sales of 100, COGS of 50, and a purchase amount of 25, the percentage should be calculated as 25% (i.e., 25/100), rather than 25/50 (the total of COGS)

Vertical Analysis = DIVIDE(SUM(PL_Unpivot[Amount]),

CALCULATE(SUM(PL_Unpivot[Amount]),

ALL(PL_Unpivot[Sub-Type])))

Re: Dax Measure for P&L vertical Analysis

Ashish_Mathur — Tue, 27 Jun 2023 23:56:57 GMT

Hi,

Share the download link of the PBI file and show the expected result.

Re: Dax Measure for P&L vertical Analysis

Nauman — Wed, 28 Jun 2023 13:52:00 GMT

Hi Ashish,

Please find below the screenshot of my desired result (Yellow highlighted columns), where each line item is divided by the total sales or net revenue.

below is my data model

Re: Dax Measure for P&L vertical Analysis

Ashish_Mathur — Wed, 28 Jun 2023 23:16:36 GMT

As requestd in my previous message, share the download link of the PBI file.