DAX Formula

[coetseem]

Good day, 

I am looking for a dax formula that can caclulate a % over each status.

Meaning Churn / Opening ARR = -2.8 but i want to do this for all statuse below in one coloum.

Each status is devided over the Opening ARR.

Reporting Status	Sum of ARR £m	% of Opening ARR
Opening ARR	111.38	100.0%
Churn	-                     3.17	-2.8%
Downsell	-                     3.99	-3.6%
Gross Retention	104.22	93.6%
Upsell	17.25	15.5%
Net Retention	121.46	109.1%
New	3.62	3.3%
Acquisitions	-	0.0%
Closing ARR	125.08	112.3%

[Ritaf1983]

Hi @coetseem 
How does your data look?
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...

[coetseem]

Account No CF_CU_NAME	Sum of ARR Value	DatePeriod	Period	Entity	Region	Reporting Status	Reporting Status Detailed
Tier1	3531.32153	P6 - Jun-23	P6	UKR	UK	Opening ARR	Opening ARR
Tier1	3531.32153	P6 - Jun-23	P6	UKR	UK	Gross Retention	Gross Retention
Tier1	256.81846	P6 - Jun-23	P6	UKR	UK	Upsell	- Upsell Revenue Stream
Tier1	3788.13999	P6 - Jun-23	P6	UKR	UK	Net Retention	Net Retention
Tier1	0	P6 - Jun-23	P6	UKR	UK	Acquisitions	- Acquisitions Revenue Stream
Tier1	3788.13999	P6 - Jun-23	P6	UKR	UK	Closing ARR	Closing ARR
Tier10	542.47853	P6 - Jun-23	P6	UKR	UK	Opening ARR	Opening ARR
Tier10	542.47853	P6 - Jun-23	P6	UKR	UK	Gross Retention	Gross Retention
Tier10	71.97887	P6 - Jun-23	P6	UKR	UK	Upsell	- Upsell Revenue Stream

[coetseem]

Account No CF_CU_NAME	Sum of ARR Value	DatePeriod	Period	Entity	Region	Reporting Status
Tier1	3531.32153	P6 - Jun-23	P6	UKR	UK	Opening ARR
Tier1	3531.32153	P6 - Jun-23	P6	UKR	UK	Gross Retention
Tier1	256.81846	P6 - Jun-23	P6	UKR	UK	Upsell
Tier1	3788.13999	P6 - Jun-23	P6	UKR	UK	Net Retention
Tier1	10	P6 - Jun-23	P6	UKR	UK	Acquisitions
Tier1	3788.13999	P6 - Jun-23	P6	UKR	UK	Closing ARR
Tier10	200	P6 - Jun-23	P6	UKR	UK	Churn
Tier10	100	P6 - Jun-23	P6	UKR	UK	Downsell
Tier10	50	P6 - Jun-23	P6	UKR	UK	New

[coetseem]

Status	Value	DAX %
Opening ARR	3531.32	100.0%
Churn	200.00	5.7%
Downsell	100.00	2.8%
Gross Retention	3531.32	100.0%
Upsell	256.82	7.3%
Net Retention	3788.14	107.3%
New	50.00	1.4%
Acquisitions	10.00	0.3%
Closing ARR	3788.14	107.3%

The Opening ARR is devided by the Opening ARR which gives me the 100% then each status is devided into the Opening ARR to give me their % but all needs to be in one column.

[v-zhouwen-msft]

Hi @Ritaf1983 ,thanks for the quick reply, I'll add further.

Hi @coetseem ,

The Table data is shown below:

Please follow these steps:
1. Use the following DAX expression to create a measure

value = SUM('Table'[Sum of ARR Value])

2.Use the following DAX expression to create a measure

Measure = 
VAR _a = MAXX(FILTER(ALL('Table'),'Table'[Reporting Status] = "Opening ARR"),[Sum of ARR Value])
RETURN DIVIDE([value],_a)

3.Final output

[coetseem]

Good day, 

This does not work on my dataset.

I have over 80 000 clients and each one have a line with a Opening ARR, Churn etc so in the data set you will need to add a few clients each with an Opening ARR, Churn etc. Below is the output that i get when i applied the measure.

[v-zhouwen-msft · Accepted solution]

Hi @coetseem ,

Is this column the sum of all your clients?

 Use the following DAX expression to create  measures

value = SUM('Table'[ARR Value])

Measure = 
VAR _a = SUMXX(FILTER(ALL('Table'),'Table'[Reporting Status] = "Opening ARR"),[ARR Value])
RETURN DIVIDE([value],_a)

If you simply want to see the values for a single user, put the user field in the slicer.