Cumulative total per category and date presented in matrix

[Anonymous]

Hello,

I would like to create a visualization which shows cumulative totals of leads per week/month/year (it's a hierarchy) and per leads stage.

In columns I have snapshot dates, but in rows leads stages. what formula should I create?

I used:

Contacts = CALCULATE('Reporting HubSpotContactDataSnapshotView'[Contacts No],

FILTER(ALLEXCEPT('Reporting HubSpotContactDataSnapshotView','Reporting HubSpotContactDataSnapshotView'[lifecyclestage]),'Reporting HubSpotContactDataSnapshotView'[Week]<=max('Reporting HubSpotContactDataSnapshotView'[Week])))

but I'm not sue it is correct as I have some 'blank values' . For me it shouldn't have happend as it is cumulative value.

[Anonymous · Accepted solution]

Hi  @Anonymous ,

You try to modify the formula to the following form:

Contacts = 
CALCULATE('Reporting HubSpotContactDataSnapshotView'[Contacts No],
FILTER(All('Reporting HubSpotContactDataSnapshotView),[lifecyclestage]=MAX([lifecyclestage] &&
'Reporting HubSpotContactDataSnapshotView'[Week]<=max('Reporting HubSpotContactDataSnapshotView'[Week])))

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Anonymous]

Thank you for your help! I'm afraid the suggested solution does not work, but please find the link to the data and expected outcome.

https://docs.google.com/spreadsheets/d/1q6ECnYvXJGbPHTpL5Ot4Q1eUeRhw8NH1/edit?usp=sharing&ouid=11326... 

Really appreciate that 🙂

[amitchandak]

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

Best way is to have week or date table and try like

Contacts = CALCULATE('Reporting HubSpotContactDataSnapshotView'[Contacts No],ALLEXCEPT('Reporting HubSpotContactDataSnapshotView','Reporting HubSpotContactDataSnapshotView'[lifecyclestage])
FILTER(allselected('Week'),'Week'[Week]<=max('Week'[Week])))

and use week from this table in visual