Forum Discussion

Gyuzal0997's avatar
Gyuzal0997
Regular Visitor
4 years ago
Solved

Row subtotal is incorrect

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.
 
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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.

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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.