Forum Discussion

Grahamwest's avatar
Grahamwest
Regular Visitor
8 years ago

Need help with formula

Hello Folks,   I got an example table with:     Batch                           Qty (Kg)          Auction no. A1025                           5                                1 A1025          ...
Anonymous's avatar
Anonymous
Not applicable
8 years ago

Hi Grahamwest,

 

Actually, you only need to switch the summary mode, then it will to show the distinct value.

 

Notice: Summary mode 'Minimum', 'Maximum', 'Median', 'Average' suitable for your requirement.

 

Measure version:

Qty(Kg) = CALCULATE(LASTNONBLANK('sample'[ Qty (Kg)],[ Qty (Kg)]),ALLSELECTED('sample'[Auction no.]))

 

 

Regards,

Xiaoxin Sheng

Grahamwest's avatar
Grahamwest
Regular Visitor
8 years ago

Hi Anonymous

 

Thanks for the reply!!!

 

I need the sum of the totals: (5+4+7) = 16

 

Please help!!!

 

Thanks,

Graham

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    HI Grahamwest,

     

    You can use below measure to achieve your requirement, I modified formula to display total qty at 'Total'.

    Qty(Kg) =
IF (
    ISFILTERED ( 'sample'[Batch] ),
    CALCULATE (
        LASTNONBLANK ( 'sample'[ Qty (Kg)], [ Qty (Kg)] ),
        ALLSELECTED ( 'sample'[Auction no.] )
    ),
    SUMX (
        SUMMARIZE (
            ALLSELECTED ( 'sample' ),
            'sample'[Batch],
            "QTY", DISTINCT ( 'sample'[ Qty (Kg)] )
        ),
        [QTY]
    )
)

     

    Regards,

    Xiaoxin Sheng

    • Grahamwest's avatar
      Grahamwest
      Regular Visitor
      8 years ago

      Hi, Anonymous

       

      You are awesome!!! This is what I wanted. Thanks a lot for your help.

       

      Just a slight problem, when I am selecting 2 auctions which have unique value/no duplicate value it is giving me below error:

       

       

      please help me on this too.

       

      Also I would like to understand the formula what it means.

       

      Thanks again!!!

       

       

      Thanks,

      Graham

      • Grahamwest's avatar
        Grahamwest
        Regular Visitor
        8 years ago

        Also,

         

        when i am selecting all of the auctions it is giving me the same value:

         

        It is working fine if i am not taking any attribute in a column.

         

        Thanks,

        Graham