PowerBI visual with cumulative total over quarters and another field

0
votes

I have the following graph created. It tracks the count of a certain even in a quarter by groups (i erased the group names and renamed them (ABC's due to sensitive data). I need the graph to show the cumulative value that is to say for example. Q1 A=1, Q2 A=3, Q3 A=5.

I have played around with quick measures but I can't seem to make them breakdown the accumulation by group, Only quarter (Q1 =1, Q2 =6, etc). I think i need to create a quick-measure of a quick-measure but I am not sure the order and what the measures would look like.

There are only 2 relevant fields: date_of_event and group

X axis: date of event (by year and quarter), group

y axis: count of date_of event

Picture of graph

Thanks

2
powerbidax

2 Answers

0
votes

I had encounter it early this week and below is my DAX for the Cumulative total measure,

Cumulative Total =

CALCULATE (
    SUMX (
        Table,
        IF ( DISTINCTCOUNT ( Table[UserID] ) > 0, 1, 0 ) //Put your Group Here
    ),
    FILTER (
        ALLSELECTED ( Table ),
        Table[InitialAccessDate]  //Date of event 
            <= MAX ( Table[InitialAccessDate] ) //Date of event
    )
)

I hope it helps!! Cheers!!

0
votes

For this, you'll definitely benefit from a date dimension and a dimension for your group. There are many template date dimensions out there, but I'm partial to mine. A group dimension for you may be as simple as just taking the distinct values of your existing [Group] field.

Time intelligence is basically always easier when your model is dimensionalized.

With that, you'd set up relationships as below:

  • 'DimDate'[Date] -1:N-> 'YourEventTable'[Date_Of_Event]
  • 'DimGroup'[Group] -1:N-> 'YourEventTable'[Group]

With that in place, you can use the built-in time intelligence functions or roll your own (examples of rolling your own in my linked date dimension repo).

Events = COUNTROWS ( 'YourEventTable' )

Events YTD = TOTALYTD ( [Events], 'DimDate'[Date] )

If you need an all-time cumulative, instead, you can use this:

Events All-time Cumulative =
VAR CurrentDate = MAX ( 'DimDate'[Date] )
RETURN
  CALCULATE (
    [Events],
    ALL ( 'DimDate' ),
    'DimDate'[Date] <= CurrentDate
  )

Make sure to always use dimension fields for axis labels and such, and never the same from the fact table.