Forum Discussion

HugoJesus's avatar
HugoJesus
Helper IV
5 years ago

Cumulative Using Summarized Data by x-axis visualizations

Hello everyone, 

 

I've the follow table with summarized data, but it's working fine with a table, but I want to show the column Open_Tickets by Calendar in x-axis visualization, this is possible?

 

This is my DAX code:

 

Total_Open_Tickets =
var TempTable =
SUMMARIZE
(
Date_Link,
Date_Link[Calendar],
"Created_Tickets",CALCULATE(count(Date_Link[id]),FILTER(Date_Link,Date_Link[Date_Type] = "Creation")),
"Closed_Tickets",CALCULATE(count(Date_Link[id]),FILTER(Date_Link,Date_Link[Date_Type] = "Closure")),
"Daily_Open_Tickets",
CALCULATE(count(Date_Link[id]),FILTER(Date_Link,Date_Link[Date_Type] = "Creation"))
-
CALCULATE(count(Date_Link[id]),FILTER(Date_Link,Date_Link[Date_Type] = "Closure"))
)
VAR LastDay =
MAX ( Date_Link[Calendar] )
VAR TempTable2 =
ADDCOLUMNS (
TempTable,
"Open_Tickets",
var _date = [Calendar]
return
SUMX(
FILTER(
TempTable,
[Calendar]<=_date
),
[Daily_Open_Tickets]
)
)
RETURN
TempTable2
 
This is the format of TempTable2:
 
How can I do it?
Any idea about this?
 
Regard's
Hugo Jesus
Need Help

17 Replies

    • HugoJesus's avatar
      HugoJesus
      Helper IV
      5 years ago

      Hi,

       

      This is a different level and my idea is to have the "Open_Tickets" in Area Chart by date.

       

      The code that I'm using is the same that you have sent before, but a little different at the end.


      Create a Measure:

       

      Total_Open_Tickets =
      var TempTable =
      SUMMARIZE
      (
      Date_Link,
      Date_Link[Calendar],
      "Created_Tickets",CALCULATE(count(Date_Link[id]),FILTER(Date_Link,Date_Link[Date_Type] = "Creation")),
      "Closed_Tickets",CALCULATE(count(Date_Link[id]),FILTER(Date_Link,Date_Link[Date_Type] = "Closure")),
      "Daily_Open_Tickets",
      CALCULATE(count(Date_Link[id]),FILTER(Date_Link,Date_Link[Date_Type] = "Creation"))
      -
      CALCULATE(count(Date_Link[id]),FILTER(Date_Link,Date_Link[Date_Type] = "Closure"))
      )
      VAR TempTable2 =
      ADDCOLUMNS (
      TempTable,
      "Open_Tickets",
      var _date = Date_Link[Calendar]
      return
      SUMX(
      FILTER(
      TempTable,
      Date_Link[Calendar]<=_date
      ),
      [Daily_Open_Tickets]
      )
      )
      return
      SUMX(TempTable2,[Open_Tickets])
       
      The result of this. .. is only showing the "Daily_Open_Tickets" instead of "Open_Tickets".
      At below, as you can see the "Open_Tickets" is the correct value and "Total_Open_Tickets" is the Measure that I've asked for help before, both are different.
      The "Total_Open_Tickets" is showing the "Daily_Open_Tickets" instead of cumulative.

      Any idea how to solve this.

       

      Regard's

      Hugo Jesus

  • Fowmy's avatar
    Fowmy
    Super User
    5 years ago

    HugoJesus 

    Try this code please:

    Total_Open_Tickets = 
VAR TempTable =
    SUMMARIZE (
        Date_Link,
        Date_Link[Calendar],
        "Created_Tickets",
            CALCULATE (
                COUNT ( Date_Link[id] ),
                FILTER ( Date_Link, Date_Link[Date_Type] = "Creation" )
            ),
        "Closed_Tickets",
            CALCULATE (
                COUNT ( Date_Link[id] ),
                FILTER ( Date_Link, Date_Link[Date_Type] = "Closure" )
            ),
        "Daily_Open_Tickets",
            CALCULATE (
                COUNT ( Date_Link[id] ),
                FILTER ( Date_Link, Date_Link[Date_Type] = "Creation" )
            )
                - CALCULATE (
                    COUNT ( Date_Link[id] ),
                    FILTER ( Date_Link, Date_Link[Date_Type] = "Closure" )
                )
    )
VAR LastDay =
    MAX ( Date_Link[Calendar] )
VAR TempTable2 =
    ADDCOLUMNS (
        TempTable,
        "Open_Tickets",
            VAR _date = [Calendar]
            RETURN
                SUMX ( FILTER ( TempTable, [Calendar] <= _date ), [Daily_Open_Tickets] )
    )
RETURN
   
ADDCOLUMNS(
    TempTable2,
    "Cummulative", 
    SUMX( FILTER(TempTable2, Date_Link[Calendar] <= EARLIER(Date_Link[Calendar])), [Daily_Open_Tickets])
)

    ________________________

    If my answer was helpful, please consider Accept it as the solution to help the other members find it

    Click on the Thumbs-Up icon if you like this reply 🙂

    YouTube  LinkedIn

    • Fowmy's avatar
      Fowmy
      Super User
      5 years ago

      HugoJesus 

      Do you still need the OPEN_TICKETS column? If not, change the code after the RETURN as below and try.

      ADDCOLUMNS(
    TempTable,
    "Cummulative", 
    SUMX( FILTER(TempTable, Date_Link[Calendar] <= EARLIER(Date_Link[Calendar])), [Daily_Open_Tickets])
)

       
      You may share a PBIX file with sample data or an excel sample file to test the same.

      You can save your files in OneDrive, Google Drive, or any other cloud sharing platforms and share the link here.

       

      -------------------------------------------------

      If my answer was helpful, please consider Accept it as the solution to help the other members find it

      Click on the Thumbs-Up icon if you like this reply 🙂

      YouTube  LinkedIn