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,

 

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Hi Grahamwest,

       

      Based on test, this formula works when I test to choose multiple unique records in slicer.

       

       

       

      Have you modified the group column or slicer column? If not, please share the sample pbix file to test.

       

      BTW, I also update the formula condition to confirm it can correct filter the total row, maybe you can try it.

      Qty(Kg) = 
IF (
    COUNTROWS ( 'sample' ) <> COUNTROWS ( ALLSELECTED ( 'sample' ) ),
    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

         

        I have tried this one too, but it is giving me same result,

         

        Can I have you email Id so that I can send you the data and that may be esier for you to work upon?

         

        Thanks,

        Graham