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          ...
Grahamwest's avatar
Grahamwest
Regular Visitor
8 years ago

Can it be done by using measure?

 

I have almost reached at the end, but now the porblem is totals. See this:

 

I tried doing it mathematically:

 

-TotalOfferedforunique = DIVIDE(SUM('Live Auction'[Offered QTY]),COUNT('Live Auction'[Batch No]))

-Distict Batch Count = DISTINCTCOUNT('Live Auction'[Batch No])

-Unique Offered QTY (MT) = [TotalOfferedforunique]*[Distict Batch Count]

 

Now the same formula is getting applied on its total, which I dont want. I want the sum of the columns.

 

When I am exporting it in excel and adding up it is giving me the unique value but not when I am seeing it in BI.

 

Thanks,

Graham

  • 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