Calculated measure is giving wrong total but correct individual value.

[Alwaysactive]

Hi,

The below created measure gives wrong total at the bottom of table visual, but when I export the data to excel, and add up the numbers then the total is correct. There are 3 other dimension fields displayed in the table visual where each row data is correct including the created measure. Below is the measure calculation.

IN_Year_1 = CALCULATE(sum(Fact_In_Year_Sales[Product_ACV]), ALLEXCEPT('Dim_Date', Dim_Date[Contract_Quarter])) * CALCULATE(sum(Finance_Pct[Finance_Pct]), Finance_Pct[Group_2_key] in values( Fact_In_Year_Sales[Group_2_key])).

I think I need to do sumx of those 3 dimension fields. Not sure how ot fix it. Any Help is appreciated

Thanks,

[v-pagayam-msft]

Hi @Alwaysactive ,
Can you please confirm whether you have resolved issue. If yes, you are welcome to share your workaround and mark it as a solution so that other users can benefit as well. This will be helpful for other community members who have similar problems to solve it faster.
Thank you.

[v-pagayam-msft]

Hi @Alwaysactive ,
I wanted to follow up on the previous suggestions regarding How to Migrate Fabric Lakehouse & Dataflows Gen2 to PPU. We would like to hear back from you to ensure we can assist you further.
If our response has addressed your query, please accept it as a solution and give a ‘Kudos’ so other members can easily find it. Please let us know if there’s anything else we can do to help.
Thank you.

Regards,
Pallavi.

[v-pagayam-msft]

Hi @Alwaysactive ,
Thank you for reaching out to the Microsoft fabric community forum. Also, thanks @SamsonTruong  for those valuable insights for this thread.
Have you got an opportunity to review the information provided by SamsonTruong .
Please feel free to contact us if you have any further questions. If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[SamsonTruong · Accepted solution]

Hi @Alwaysactive ,

Please try the adjusted DAX measure below and let me know if it achieves your desired result:

IN_Year_1 =
SUMX(
    SUMMARIZE(
        Fact_In_Year_Sales,
        Dim_Date[Contract_Quarter],
        Fact_In_Year_Sales[Group_2_key],
        "RowCalc",
            CALCULATE(
                SUM(Fact_In_Year_Sales[Product_ACV]),
                ALLEXCEPT('Dim_Date', Dim_Date[Contract_Quarter])
            ) *
            CALCULATE(
                SUM(Finance_Pct[Finance_Pct]),
                Finance_Pct[Group_2_key] IN VALUES(Fact_In_Year_Sales[Group_2_key])
            )
    ),
    [RowCalc]
)

If this helped, please mark it as the solution so others can benefit too. And if you found it useful, kudos are always appreciated.

Thanks,

Samson

Connect with me on LinkedIn

Check out my Blog

Going to the European Microsoft Fabric Community Conference? Check out my Session