Cumulative Using Summarized Data by x-axis visualizations

[HugoJesus]

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

[Fowmy]

@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]

@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

[HugoJesus]

But the code you have sent is the same.

[Fowmy]

@HugoJesus 

I replaced TempTable2 with TempTable.

________________________

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

[HugoJesus]

I still receive the same error, "This expression refers to multiple columns..." using the the "TempTable".

Regards,

Hugo Jesus

[Fowmy]

@HugoJesus 

Please share your PBIX  with sample data removing any confidential data / information.

________________________

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

[HugoJesus]

@Fowmy 

How can I post the PBIX?

[HugoJesus]

Hi @Fowmy 

I've found how to share the PBIX, is now available to download, in the below link.

Password: 22092020

Regards

Hugo Jesus

PBIX
Example.PBIX 

[Fowmy]

@HugoJesus 

You have tried to add the code as a new column. You need to add it as a new table. Go to Modeling Tabl > New Table and paste it. I see the Cumulative Total coming up. I added it as a new Table 

You can download the file: 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

[HugoJesus]

@Fowmy 

Yes, I understand that and if you saw the PBIX, already exist a table with that code "Total_Open_Tickets".

But, my idea is to have an x-axis visualizations with that column, instead of a table.

Something like this, but with that column "Open_Tickets".

If look up to the example (image below), you saw two lines, the bottom line (Open_Tickets) that's the correct value but inserted manually, and the second line I'm using the code that you have sent but unsing in a Measure.

Regard's

Hugo Jesus

[Fowmy]

@HugoJesus 

Sorry, I did not understand what sort of visualization you are after.
Below is your data, now can you explain how the expected result should come out?

________________________

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

[HugoJesus]

@Fowmy 

My idea is only to show the column "Open_Tickets" in a x-axix visualization by Calendar.

Did you understand?

Thanks.

Regard's

Hugo Jesus

[HugoJesus]

@Fowmy  or @amitchandak 
Any idea about this?

[HugoJesus]

@Fowmy 

@amitchandak 

Hello everyone, 

Any ideia about this? I need help urgently.

Thanks,

Hugo Jesus

[HugoJesus]

Hi @Fowmy 

I'm receiving the follow error:

[amitchandak]

@HugoJesus , Your table structure is not clear.

I have a blog on a similar topic : https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

And cumulative of open(current employee added to file attached after signature .

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[HugoJesus]

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