cumulative by group

[JoaoTrindade]

Hi,

I'm trying to do a cumulative sum by group. I've tried different solutions but none seems to work.

I have a table with millions of entries of booking made and they all are grouped by channel group

I was able to create a cumulative sum for the number of nights as in above picture.

The main problem I get is that it does not sum indevidualy per group when all groups are selected. It groups the nights and shows the same value for all.

this is how I'm calculating the cumulative sum

cumulative nights = 
CALCULATE([nr_nights total for group],
FILTER(ALLSELECTED('gc_reports bookings'),
'gc_reports bookings'[book_date].[Date]<= MAX ('gc_reports bookings'[book_date].[Date])))

The nr nights total for group is a quick mesure like this

nr_nights total for group = 
CALCULATE(
	SUM('gc_reports bookings'[nr_nights]),
	ALLSELECTED('gc_reports channel_groups'[group])
)

Once you select a specific group, all works.

Sorry if this is a dumb question but what am I doing wrong here?

[parry2k]

@JoaoTrindade not sure if you need allselected in this measure

nr_nights total for group = 	SUM('gc_reports bookings'[nr_nights])

[JoaoTrindade]

that filed is a Quick Mesure of total of nights grouped by channel Group. That is the code that comes out of the quick mesure .

[parry2k]

@JoaoTrindade did you changed it as I suggested?

[JoaoTrindade]

I did now but it did did not change anything

[parry2k]

@JoaoTrindade can you share pbix file, remove any sensitive information before sharing.

[JoaoTrindade]

@parry2k I've posted it here

[parry2k]

@JoaoTrindade seems like you missed the link

[JoaoTrindade]

@parry2k I've edited the reply and it got lost. Sorry for that.

Link

[parry2k]

@JoaoTrindade why you are sharing msi file?

[JoaoTrindade]

@parry2k  that goes to show you the state I'm in...  🙂 

I selected the wrong file. Here it is

[parry2k]

@JoaoTrindade I think this is what you are looking for, correcT?

[parry2k · Accepted solution]

@JoaoTrindade here is the measure

cumulative nights = 
CALCULATE(
    [total nights],
    FILTER(
        ALLSELECTED('gc_reports bookings'[book_date].[Date]),
        'gc_reports bookings'[book_date].[Date] <= MAX ('gc_reports bookings'[book_date].[Date])
    )
)

also I noticed there is lot of both direction cross filter in your model whcih is a bad idea. anyhow that is seperate topic.

[JoaoTrindade]

@parry2k  Thanks a million Parry! This works to perfection.

[JoaoTrindade]

@parry2k I was now looking in more detail at the data and the cumulative now it not accounting for the specific booking date selected. It is cumulating for all time. Why is that?

[JoaoTrindade]

Absolutely! What's the magic trick?