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

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

Hi Anonymous ，

 

If the value of Qty_in Gen under each document no. is same, you can simply use a measure for calculation as below:

 

Measure 3 = CALCULATE(MAX('Table (2)'[Qty_ in Gen]),ALLEXCEPT('Table (2)','Table (2)'[No_]))

 

But if there may be different values of Qty_in Gen under each document no. ,the measure needs to be modified 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])

 

Of course, measure 2 can also be used to calculate in situation 1 ,but measure 3 is more simpler, all depends on you.

 

For the related .pbix file, pls click here.

 

Best Regards,

Kelly