Forum Discussion

Ubaid ur Rahman's avatar
Ubaid ur Rahman
Copper Contributor
Mar 28, 2019
Solved

Running total in Pivot table (Monthwise incrementation by distinct count)

Dear Experts,

 

I need help regarding running total in pivot.

Please find the attachement and it is better explained there.

 

thank you in advance for your quick response.

excel
  • SergeiBaklan's avatar
    SergeiBaklan
    Mar 28, 2019

    Ubaid ur Rahman , that's not the same as in your table. If you have distinct count for the one month and another one, total distinct count won't be a sum since some suppliers are repeated in full period. Thus total for two months shall be less than sum of distinct count for each of two months. With that logic that is like

    DistinctCountSuppliersTotal:=CALCULATE (
    DISTINCTCOUNT([Supplier No.]),
    FILTER ( ALL ( Table1 ), Table1[Purchase date] <= MAX (Table1[Purchase date] ) )
)

    If distinct count only for the year

    DistinctCountSuppliers:=CALCULATE (
    DISTINCTCOUNT([Supplier No.]),
    FILTER ( ALLEXCEPT ( Table1,Table1[Year] ), Table1[Month] <= MAX (Table1[Month] ) )
)

    I added both, please check in the file.

     

    Or you assume another logic?

15 Replies

Sort By
  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    Mar 28, 2019

    Ubaid ur Rahman , that's not the same as in your table. If you have distinct count for the one month and another one, total distinct count won't be a sum since some suppliers are repeated in full period. Thus total for two months shall be less than sum of distinct count for each of two months. With that logic that is like

    DistinctCountSuppliersTotal:=CALCULATE (
    DISTINCTCOUNT([Supplier No.]),
    FILTER ( ALL ( Table1 ), Table1[Purchase date] <= MAX (Table1[Purchase date] ) )
)

    If distinct count only for the year

    DistinctCountSuppliers:=CALCULATE (
    DISTINCTCOUNT([Supplier No.]),
    FILTER ( ALLEXCEPT ( Table1,Table1[Year] ), Table1[Month] <= MAX (Table1[Month] ) )
)

    I added both, please check in the file.

     

    Or you assume another logic?

    • Ubaid ur Rahman's avatar
      Ubaid ur Rahman
      Copper Contributor
      Mar 28, 2019

      SergeiBaklan Thank you for your quick response.

       

      Yes that is correct but now the problem is when I apply it to my original file it is somehow not working.

      For confidential reason I did extract some elements which I uploaded.


      See in the picture.

Resources