Forum Discussion

MatevzP's avatar
MatevzP
Icon for Helper I rankHelper I
4 years ago
Solved

Show intermediate sum result

Hello everyone,

 

First, let me excuse myself for probably a very basic question. Also, English is not my native language so i'm not even sure the title is correct, but let me show what i need, it'll be the easiest.

So i have an excel document showing number of documents uploaded on each day. I'd like to show a graph that is constantly increasing, not actual values per day.

 

 

Since my table doesn't include a value of actual documents uploaded on each day, i go through and instead of CALCULATE(SUM()), i use COUNT, and count document titles. Same end result: total sum is correct, but as i said, i'd like to get a graph that is only ever going up, not up and down, for the lack of better words.

In Java, i'd do a loop, use a temp integer variable and then increase integer on each cycle and print it out.
But i'm not sure how to approach the problem with DAX.

 


(Just a visual representation of what i think is going on, not actual table, i only have one table with data)


Thank you in advance and have a nice day 🙂

  • amitchandak's avatar
    amitchandak
    4 years ago

    MatevzP , using date table joined to you table (date from date table used in visual

    Cumm = CALCULATE(SUM(Table[documents]),filter(allselected('Date'),'Date'[date] <=max('Date'[date])))

     

    or

    Cumm = CALCULATE(SUM(Table[documents]),filter(allselected('Table'),'Table'[date] <=max('Table'[date])))

  • tamerj1's avatar
    tamerj1
    4 years ago

    MatevzP 

    In your chart, you're still using the date from the same table, right?

    try this code

    test 3 =
CALCULATE (
    COUNTROWS ( WP1_Report ),
    ALLEXCEPT ( WP1_Report, WP1_Report[Review Status] ),
    WP1_Report[Last Upload] <= MAX ( WP1_Report[Last Upload] )
)

7 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    MatevzP , using date table joined to you table (date from date table used in visual

    Cumm = CALCULATE(SUM(Table[documents]),filter(allselected('Date'),'Date'[date] <=max('Date'[date])))

     

    or

    Cumm = CALCULATE(SUM(Table[documents]),filter(allselected('Table'),'Table'[date] <=max('Table'[date])))

    • MatevzP's avatar
      MatevzP
      Icon for Helper I rankHelper I
      4 years ago

      Hi, thank you for your reply!

      It seems like (to my untrained eye), that you are working with two tables here. One being table[documents] and another one being table[date].

      In reality, i only have one table. I probably didn't explain it well in OP.
      This is how it looks like:

    • MatevzP's avatar
      MatevzP
      Icon for Helper I rankHelper I
      4 years ago

      Ok, it works!

      I took your DAX formula and somehow applied to my scenario. Not exactly sure how it works, but it does.


      month, ok

       

      DAX:

       

      test = CALCULATE(COUNT(WP1_Report[Last Upload]), FILTER(ALLSELECTED(WP1_Report[Last Upload].[Date]), WP1_Report[Last Upload].[Date]<=MAX(WP1_Report[Last Upload])))

       

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Hi MatevzP 

    this is called Runnimg Total or Comulative Count. You can try

    # Documents RT =
CALCULATE (
    DISTINCTCOUNT ( Table[Folder Name and Path] ),
    Table[Last Upload] <= MAX ( Table[Last Upload] )
)
    • MatevzP's avatar
      MatevzP
      Icon for Helper I rankHelper I
      4 years ago

      Hi,

      Thanks for your reply as well, but answer from Mr. amitchandak works better in my scenario.

       

      I did two versions of your DAX, one distinct and one just count.

      DISTINCTCOUNT (folder paths are always the same, hence 1)

       

      COUNT (counts, but as originally, goes up and down)

       

      DAX:

      test 2 = 
CALCULATE(
    COUNT(WP1_Report[Folder Name and Path]),
    WP1_Report[Last Upload] <= MAX(WP1_Report[Last Upload])
)

test 2 = 
CALCULATE(
    DICTINCTCOUNT(WP1_Report[Folder Name and Path]),
    WP1_Report[Last Upload] <= MAX(WP1_Report[Last Upload])
)

       

      • tamerj1's avatar
        tamerj1
        Icon for Community Champion rankCommunity Champion
        4 years ago

        MatevzP 

        In your chart, you're still using the date from the same table, right?

        try this code

        test 3 =
CALCULATE (
    COUNTROWS ( WP1_Report ),
    ALLEXCEPT ( WP1_Report, WP1_Report[Review Status] ),
    WP1_Report[Last Upload] <= MAX ( WP1_Report[Last Upload] )
)