how to get the correct total of a measure

[Rachel_Lei]

I created this measure to calculate Price Gain = ([Avg Price]-[PY Avg Price])*[Qty.]/1.13, it is all good on the item level, but the total value is also sticking to this calculation logic, what I need is the sum of Price Gain of all items.

[Rachel_Lei]

I used a not smart way to solve this, create two tables of data 2021/2022, and add a calculated column to item table, then use a measure to calculate the column....

Could someone please help me check what is wrong with this column, I get same result for each item:

PriceGain2022=CALCULATE(

    SUM('NAV_Value Entry'[Invoiced Quantity]),

    FILTER('NAV_Value Entry',

    YEAR('NAV_Value Entry'[Posting Date])=2021

    )

    )

[v-yiruan-msft · Accepted solution]

Hi @Rachel_Lei ,

Please update the formula of measure [PriceGain2022] as below and check if it can return your expected result...

PriceGain2022=CALCULATE(

    SUM('NAV_Value Entry'[Invoiced Quantity]),

    FILTER('NAV_Value Entry',

    YEAR('NAV_Value Entry'[Posting Date])=2022

    )

    )

And for the incorrect total value of measure, you can create another measure as below and put it onto the visual to replace the original one [Price Gain] :

Measure = SUMX ( 'NAV_Value Entry'[item], [Price Gain] )

Besides that, you can refer the following links to try to solve the incorrect total values problem...

Why Your Total Is Incorrect In Power BI - The Key DAX Concept To Understand

Dax for Power BI: Fixing Incorrect Measure Totals

If the above one can't help you get the desired result, please provide some sample data in your table 'NAV_Value Entry' (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[PVO3]

You could start trying below. Otherwise I suggest to send an example file.

Measure =

SUMX(

    itemtable,

    ([Avg Price]-[PY Avg Price])*[Qty.]/1.13

)

[Rachel_Lei]

Avg Price and qty are also measures, I have a item table with only item code column, and another sales revenuw talble which incudes item code/qty/rev/date/customer, so one item could have many lines of rev and qty from different cusotmers and dates.