Dynamic Weighted Average Margin for a Product on a Specific Date

[MAlimalik]

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

)

)

[v-pagayam-msft]

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.

[v-pagayam-msft · Accepted solution]

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.

[v-pagayam-msft]

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.

[MAlimalik]

Top Contributor kindly look into it: @techies @Bibendum @Jihwan_Kim 

[techies]

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)