Row subtotal is incorrect

[Gyuzal0997]

Hi, everyone.

I am facing the problem of displaying row subtotal value correctly. 

In my data I have a hierarchy where there are  outlet codes connected with a person or trade marketer. The goal is to calculate sales fact volume. The formula used is like this 

Sales Fact =

        VAR DSDSalesData =

            CALCULATE (

                SUM ( 'Sales - Fact'[Volume in U] ),

                FILTER ( Sales, Sales[Sales type] = "DSD Sales Data" )

            )

        VAR Alternative =

            CALCULATE (

                SUM ( 'Sales - Fact'[Volume in U] ),

                FILTER ( Sales, Sales[Sales type] = "SD Subdistributor" )

            )

                + CALCULATE (

                    SUM ( 'Order Item - Fact'[Quantity] ) * 20,

                    'Order'[Type] = "Transfer Order",

                    'Order'[Deleted] <> "Y"

                )

        RETURN

            IF ( DSDSalesData > 0, DSDSalesData, Alternative )

When I expand my sales fact value to the lowest level of hierarchy ( outlet code)- the value is right, however when I want to show the total on the person level- value is totally different.

Can you suggest any ideas on how to fix this issue?

Thanks in advance.

[Anonymous · Accepted solution]

Hi @Gyuzal0997 ,

Please have a try.

Select the only column in the table that has no duplicate values, such as a date column, or create an index column.

Then create a measure based on [sales fact].

var _b = SUMMARIZE('sales','sales'[date] or index column,"aaa",'sales'[sales fact])
return
IF(HASONEVALUE('sales'[date] or index column),'sales'[sales fact],SUMX(_b,[aaa]))

If I have misunderstood your meaning, please provide more details with screenshots.

Best Regards

Community Support Team _ Polly

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

[amitchandak]

@Gyuzal0997 , You have force visual group by the context in the formula . I should be some common dimesion column

Sumx(Values(Table[Column]), IF ( DSDSalesData > 0, DSDSalesData, Alternative ) )

Use summarize if there are more than one column

refer if needed https://www.youtube.com/watch?v=ufHOOLdi_jk