Totals Not Coming Correct Virtual Table

[rit372002]

Hi,

I am having difficulty to calculate Totals correctly in my virtual table . Here is my code. Individual rows comes correctly. I put 6 over there to make a sense.

I just need to count a column to get value of 3,960,891.

I tried HasOneFilter as well but not sure, what to write on the second half of the IF statement?

    New Measure Numerator =
VAR __summarytable =
    ADDCOLUMNS (
        SUMMARIZE ( 'Sales Order Detail', Retailer[Retailer AB Number] ),
        "@Total", CALCULATE (
            SUM ( 'Sales Order Detail'[Custom Quantity] ),
            'Order Type'[Order Type Transaction Group] = "Sales"
        ),
        "@Brand Product Line Return Flag", IF ( ISBLANK ( [Return/Dump Quantity] ), "NO", "YES" )
    )
VAR __total =
    SUMX ( __summarytable, [@Total] )
VAR __returnstotal =
    IF (
        HASONEFILTER ( 'Product'[Product Marketing Brand] ),
        SUMX (
            FILTER ( __summarytable, [@Brand Product Line Return Flag] = "YES" ),
            [@Total]
        ),
        6
    )
RETURN
__returnstotal

Thanks,
Ritesh

[joedias]

@ rit372002 Welcome to the ENT DNA community… Can you post a pbix file with some sample data that reflects the issue you referring to.

Thanks,
–Joe

[EnterpriseDNA]

Hi @rit372002, we aim to consistently improve the topics being posted on the forum to help you in getting a strong solution faster. While waiting for a response, here are some tips so you can get the most out of the forum and other Enterprise DNA resources.

• Use the forum search to discover if your query has been asked before by another member.

• When posting a topic with formula make sure that it is correctly formatted to preformated text </>.

• Use the proper category that best describes your topic

• Provide as much context to a question as possible.

• Include demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

I also suggest that you check the forum guideline https://forum.enterprisedna.co/t/how-to-use-the-enterprise-dna-support-forum/3951. Not adhering to it may sometimes cause delay in getting an answer.

Please be sure to check out our DAX Clean Up tool it’s a great way to ensure to make your DAX code easy to read. Thanks!

https://analysthub.enterprisedna.co/dax-clean-up

[rit372002]

Test Returns Excel.pbix (70.2 KB)

Here is the sample pbix and picture of what I am looking for.

Thanks for your help.

Ritesh

[AntrikshSharma]

Here are 4 versions for the Quantity to be counted measure.

QUANTITY TO BE COUNTED =
CALCULATE (
    [_Shipment Quantity] + [_Return Quantity],
    FILTER (
        SUMMARIZE (
            'Sales Order Detail',
            'Product'[Product Name],
            Retailer[Retailer Name]
        ),
        [_Shipment Quantity] > 0
        && [_Return Quantity] > 0
     )
)

QUANTITY TO BE COUNTED =
CALCULATE (
    [_Shipment Quantity] + [_Return Quantity],
    FILTER (
        CROSSJOIN (
            VALUES ( 'Product'[Product Name] ),
            VALUES ( Retailer[Retailer Name] )
        ),
        [_Shipment Quantity] > 0
            && [_Return Quantity] > 0
    )
)

QUANTITY TO BE COUNTED = 
SUMX(
    FILTER(
        SUMMARIZE(
            'Sales Order Detail',
            'Product'[Product Name],
            Retailer[Retailer Name]
        ),
        [_Shipment Quantity]>0 && [_Return Quantity]>0 
    ),
    [_Shipment Quantity]+[_Return Quantity]
)

QUANTITY TO BE COUNTED = 
SUMX(
    FILTER(
        CROSSJOIN(
            VALUES( 'Product'[Product Name] ),
            VALUES( Retailer[Retailer Name] )
        ),
        [_Shipment Quantity]>0 && [_Return Quantity]>0 
    ),
    [_Shipment Quantity]+[_Return Quantity]
)

The measures work for your second issue as well.

[Nick_M]

You can also nest row contexts:

QUANTITY TO BE COUNTED = 
SUMX( 
    VALUES( 'Product'[Product Name] ),                         
        SUMX(
            VALUES( Retailer[Retailer Name])                    
                ,VAR _Return =[_Return Quantity]            
                VAR _Ship = [_Shipment Quantity]
                VAR _ShipReturn = _Return + _Ship
                RETURN
                IF( 
                    NOT ISBLANK(_Return)                        
                    ,_ShipReturn                                
                )
        )
)

Also be sure to change your data model so that your filters are not set as bi-directional

[Nick_M]

@AntrikshSharma,
Always good to see how others come up with solutions. I took a look at yours and have a few notes (if you dont mind )

When you FILTER over a SUMMARIZE table it results in the Storage Engine (i.e. the fast one) not being able to complete that task and has to get some help from the Formula Engine, resulting in CallbackID, which degrades performance. When we run in Dax Studio, we can see that happening.

Crossjoin doesnt suffer from that issue, but doing a cross join will bring in more data that it is needed since crossjoin produces all the possible combinations, which can bring in more data that is necessary.

Not a huge deal in a small model like this. Just wanted to share my thoughts since I’ve been really getting into performance tuning lately.

-Nick

[AntrikshSharma]

Thanks a lot @Nick_M, this is really helpful, that’s a new learning for me! I used to think that CallBackDataID only appears in cases where SE needs some help from FE with function that are not available for SE. Now I am heading over to test my code in DAX Studio!!!

[rit372002]

Thank you @AntrikshSharma , @Nick_M. You guys are so smart! Still so much to learn.

It works perfectly for the excel data I created. But embarrasingly, I tried to put it in my actual pbix and I am getting very inflated number.

Here is my “Quantity to be counted”, Returns are negative number, hence less than 0.

    QUANTITY TO BE COUNTED 3 = 
SUMX(
    FILTER(
          VALUES( Retailer[Retailer Name] )
        ,
        [Net Sales]>0 && [Return Quantity]<0 
    ),
    [Net Sales]
)

I just want the total where it says 6 to be the sum of “New Measure Numerator” : so it should be: 3,999,299.

Here is my “New Measure Numerator”:

[Nick_M]

@rit372002
any chance you can upload another sample with that type of data?

[EnterpriseDNA]

Hi @rit372002, did the response provided by contributors help you solve your query? If not, how far did you get, and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!

[rit372002]

Thanks. Yes, it resolved for my sample input but I think I may have to be more creative to create exact problem.

But really good answers. Thanks!

Ritesh