topic Dynamic Weighted Average Margin for a Product on a Specific Date in DAX Commands and Tips

Dynamic Weighted Average Margin for a Product on a Specific Date

MAlimalik — Fri, 04 Apr 2025 21:55:16 GMT

Hi Guys, need urgent help.

I want to show Weighted Average Margin agsinst Date and Product. I have prepared a DAX but it is gowing overall Margin if you see in the last row of the total. Following is my DAX. Kindly help me what should I do.

Weighted Avg Margin = (

Var dailytotalsales =

Calculate(

Sum(April_2025[Total Slaes]),

Allselected(),

Values( April_2025[InvoiceDate].[Date])

)

Var dailytotalcost =

Calculate(

Sum(April_2025[Total Cost]),

Allselected(),

Values( April_2025[InvoiceDate].[Date])

)

RETURN

Divide(

(dailytotalsales + dailytotalcost),

dailytotalsales

)

Re: Dynamic Weighted Average Margin for a Product on a Specific Date

MAlimalik — Fri, 04 Apr 2025 21:57:12 GMT

Top Contributor kindly look into it: techies Bibendum Jihwan_Kim

Re: Dynamic Weighted Average Margin for a Product on a Specific Date

techies — Sun, 06 Apr 2025 06:14:10 GMT

Hi MAlimalik please try this

Weighted Avg Margin =
VAR TotalSales = SUM(April_2025[Total Slaes])
VAR TotalCost = SUM(April_2025[Total Cost])
RETURN
DIVIDE((TotalSales - TotalCost), TotalSales)

Re: Dynamic Weighted Average Margin for a Product on a Specific Date

Anonymous — Mon, 07 Apr 2025 10:13:05 GMT

Hi MAlimalik ,
Thank you techies for the prompt response!

Please use the below DAX:-

Avergare margin fixed:

Weighted Avg Margin (Fixed) =

VAR SummaryTable =

SUMMARIZE(

April_2025,

April_2025[InvoiceDate],

April_2025[Product],

"Sales", SUM(April_2025[Total Slaes]),

"Cost", SUM(April_2025[Total Cost])

)

VAR WeightedMargin =

SUMX(

SummaryTable,

VAR Sales = [Sales]

VAR Cost = [Cost]

RETURN DIVIDE(Sales - Cost, Sales) * Sales

)

VAR TotalSales =

SUMX(SummaryTable, [Sales])

RETURN

DIVIDE(WeightedMargin, TotalSales)

I tried to recreate it with sample data.Please refer the attached file.

Thank you fot being a part of Microsoft Fabric Community Forum!

If this answer meets your requirement,consider accept it as solution.

Regards,
Pallavi.

Re: Dynamic Weighted Average Margin for a Product on a Specific Date

Anonymous — Thu, 10 Apr 2025 09:43:52 GMT

Hi MAlimalik ,
I wanted to check in on your situation regarding the issue. Have you resolved it? If you have, please consider marking the reply that helped you or sharing your solution. It would be greatly appreciated by others in the community who may have the same question.
Thank you.

Re: Dynamic Weighted Average Margin for a Product on a Specific Date

Anonymous — Mon, 14 Apr 2025 06:25:24 GMT

Hi MAlimalik ,
I just wanted to kindly follow up to see if you had a chance to review the previous response provided by us. I hope it was helpful. If yes, please Accept the answer so that it will be helpful to others to find it quickly.

Thank you.