Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

DAX SUM HELP

  Ho can I calculate the sum of Qty in GRN. QTy in GRN is repeating for each line item, but i need to take the value once per each document no. Please help on this?  
Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    6 years ago

    Hi Anonymous ，

     

    You can use the measure below:

     

    Measure 2 =
CALCULATE (
    SUMX (
        SUMMARIZE (
            'Table (2)',
            'Table (2)'[No_],
            "total", SUMX ( DISTINCT ( 'Table (2)'[Qty_ in Gen] ), 'Table (2)'[Qty_ in Gen] )
        ),
        [total]
    ),
    FILTER ( 'Table (2)', 'Table (2)'[QC Result] = "Accepted without Deviation" )
)

    Best Regards,

    Kelly

Anonymous's avatar
Anonymous
Not applicable
6 years ago

For each line item, test result is different. GRN quantity is defined in header. And I made on join between header and line item. For that reason, GRN quantity is repeating in every line.

v-kelly-msft's avatar
v-kelly-msft
Icon for Community Support rankCommunity Support
6 years ago

Hi Anonymous ,

 

If so,then use the measure as below:

 

 

Measure 2 = 
SUMX(SUMMARIZE('Table (2)','Table (2)'[No_],"total",SUMX(DISTINCT('Table (2)'[Qty_ in Gen]),'Table (2)'[Qty_ in Gen])),[total])

 

 

Best Regards,

Kelly

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    I want to add one more filter, can you please help on this

    QC[QC Result] = "Accepted"
  • v-kelly-msft's avatar
    v-kelly-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Anonymous ，

     

    You can use the measure below:

     

    Measure 2 =
CALCULATE (
    SUMX (
        SUMMARIZE (
            'Table (2)',
            'Table (2)'[No_],
            "total", SUMX ( DISTINCT ( 'Table (2)'[Qty_ in Gen] ), 'Table (2)'[Qty_ in Gen] )
        ),
        [total]
    ),
    FILTER ( 'Table (2)', 'Table (2)'[QC Result] = "Accepted without Deviation" )
)

    Best Regards,

    Kelly