topic Column Total does not match in DAX Commands and Tips

Column Total does not match

Anonymous — Tue, 23 Jul 2024 03:40:04 GMT

Hi,

I need your help. I have a Purchase Price Variance report where I needed to show in a table:

These items are sourced from the same source table 'me2l' with a date range of 1 Jan 2021 to present 21 July 2024

1. Date Range - 1 Jan 2024 to 21 July 2024

1. Material item - sourced from "me2l"

2. Last year's average unit price - New Measure = Net Price per Unit LY = CALCULATE(AVERAGE('me2l'[Net Price per Unit]),DATEADD('DATE TAB'[Date],-1,YEAR); sourced from "me2l"

3. This year's average unit price - New Measure = Net Price per Unit = [Net Price]/[Price unit], sourced from "me2l"

4. This year's total ordered quantity - sourced from "me2l"

5. Currency - sourced from "me2l"

6. Exchange Rate - sourced from "me2l"

7. PPV Total = difference between 2 & 3 x 4 x 6. Unfortunately, the column total is incorrect when I download it in Excel and compare it. See New Measure = PPV TOTAL = SUMX(values('me2l'[Material]),[PPV Unit Price 2])*round(average('me2l'[Exchange Rate]),4). Where, another New Measure is created PPV Unit Price 2 = CALCULATE(AVERAGE([Net Price per Unit])-[Net Price per Unit LY])*sum('me2l'[Order Qty-adj])

See screenshot:

Unfortunately, the column total in PPV TOTAL doesn't jibe or is incorrect when you download it in Excel and validate the total column.

May I seek your assistance please in solving it. Much appreciated!

Noel Cacnio

Email: noel.cacnio@jgspetrochem.ph

Re: Column Total does not match

Anonymous — Wed, 24 Jul 2024 02:42:11 GMT

Hi Anonymous ,

Are you referring to the fact that PPV Total is different in power bi and excel after exporting data to excel?

You can check if there are any filters applied on the visual object, sometimes the data transformations or filters applied in Power BI may not be reflected in the exported data.

Due to rounding differences between Power BI and Excel, round() may cause differences, you can try to remove this function to see if the values are still different in power bi and excel

Check the official documentation below for the limitations related to export data:

Export data from a Power BI visualization - Power BI | Microsoft Learn

This is the related document, you can view this content:

Solved: Sum Column Total of exported data is different to ... - Microsoft Fabric Community

Solved: Wrong totals, different than export - Microsoft Fabric Community

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Column Total does not match

Anonymous — Wed, 24 Jul 2024 23:53:32 GMT

Noted and thanks for taking time responding to my inquiry.

Re: Column Total does not match

Anonymous — Thu, 25 Jul 2024 00:36:36 GMT

Hi all,

I am validating the one in Power BI and the one extracted to Excel. See sample item below:

The one in Power BI report has no value in PPV since the Average Unit Rate in Current Year is the same as the Average Unit Rate in Last Year. However, the one extracted to Excel somehow erroneously generated a difference of (36,119.29).

How is that possible? Could it be that it has something to do with the PPV TOTAL measure?

Re: Column Total does not match

Anonymous — Thu, 25 Jul 2024 02:07:11 GMT

Hi all,

We were able to identify the issue. Instead of averaging the Unit Price, we instead get the Average of PO value / Ordered Qty to arrive at the Average unit price. We also used the Round (x).

Thank you to Yangliu for your inputs and suggestions.