Total Sum of Visual Table incorrect

[Matt_JEM]

Good afternoon All.

I have a table where I have filtered out certain SalesOrder numbers. The total of Value of  all the SalesOrders is shown at the bottom. This total is correct if I don't filter out any SalesOrder numbers, but is incorrect if I filter out certain SalesOrder Numbers. How do I fix this so that the total reflects to correct value for the selected line. The correct total is 6 039 954.50 and not as per attached picture.

I thank you in advance for your help.

Regards

Matt

[Ajithkumar_P_05]

TotalSales =

SUMX (

    SUMMARIZE (

        'SalesTable',

        'SalesTable'[SalesOrder],

        'SalesTable'[Customer],

        'SalesTable'[CustomerPoNumber],

        'SalesTable'[OrderStatus],

        "@SalesValue", [SalesValue]

    ),

    [@SalesValue]

)

Try this measure. Use the table on the 'many' side of the relationship that contains the Sales Value.

[Matt_JEM]

Good morning  @Irwan 

When I build the visual thable the SalesOrder number comes from a table called " PoNumbersNotInFactory"and the value of the sales comes from a table called " vw_Rep_Sales_BackOrder_Offer". I hope this helps.

I thank you in advance for your support.

[Anonymous]

Hi @Matt_JEM ,

As you mentioned in your reply, there are two tables in your example data.

So I think you can give a part of the example data and the relationship between these two tables.

Best Regards

Yilong Zhou

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

[dharmendars007]

Hello @Matt_JEM , 

To make the total reflect the correct value for the filtered rows, you can modify your measure to account for the filters in the table, adjusted Measure below for Correct Totals

Filtered Total Sales =
SUMX(VALUES(SalesOrder[SalesOrder]),
CALCULATE(SUM(SalesOrder[SalesAmount])))

If you find this helpful , please mark it as solution which will be helpful for others and Your Kudos/Likes 👍 are much appreciated!

Thank You

Dharmendar S

LinkedIN 

[Matt_JEM]

Good morning Dharmendar S.

Thank you for your reply. I get exactly the same result. The problem is not calculating the SalesOrder values, the problem I have is that when I display the values in a visual table, the total displayed at the bottom is the total of all the sales orders and not the total of the sales orders that is left after implementing a filter.

I hope this make more sense. Once again thank you for the help.

Regards

Matt

[Irwan]

hello @Matt_JEM 

i am not sure but the Total is just sum of the data above.

so if the Total is not as expected, maybe the filter does not work or the calculation DAX is not working?

Thank you.