How to get totals correct?

[Ritesh_Air]

Hi,

I am having difficulty to calculate Totals correctly. 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

[parry2k]

@Ritesh_Air it will be much easier if you paste a sample data with the expected output, rather than posting DAX, and then someone has to reverse engineer your code.

Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[Ritesh_Air]

@parry2k 

https://drive.google.com/drive/folders/12C0V1_wQmMRjuOMCDHy0aY7eF0-a2NeS?usp=sharing

Here is the pbix and the screenshot.

side note: They should have a function to sum the column. There are so many hoops to go through to get the totals and I always get confused.

Thanks for your help.

Ritesh

[v-diye-msft · Accepted solution]

Hi @Ritesh_Air 

Kindly find my below results:

Pbix attached.