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