Forum Discussion

coetseem's avatar
coetseem
Frequent Visitor
2 years ago
Solved

DAX Formula

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 statu...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

coetseem's avatar
coetseem
Frequent Visitor
2 years ago
Account No CF_CU_NAMESum of ARR ValueDatePeriodPeriodEntityRegionReporting StatusReporting Status Detailed
Tier13531.32153P6 - Jun-23P6UKRUKOpening ARROpening ARR
Tier13531.32153P6 - Jun-23P6UKRUKGross RetentionGross Retention
Tier1256.81846P6 - Jun-23P6UKRUKUpsell   - Upsell Revenue Stream
Tier13788.13999P6 - Jun-23P6UKRUKNet RetentionNet Retention
Tier10P6 - Jun-23P6UKRUKAcquisitions   - Acquisitions Revenue Stream
Tier13788.13999P6 - Jun-23P6UKRUKClosing ARRClosing ARR
Tier10542.47853P6 - Jun-23P6UKRUKOpening ARROpening ARR
Tier10542.47853P6 - Jun-23P6UKRUKGross RetentionGross Retention
Tier1071.97887P6 - Jun-23P6UKRUKUpsell   - Upsell Revenue Stream
coetseem's avatar
coetseem
Frequent Visitor
2 years ago
Account No CF_CU_NAMESum of ARR ValueDatePeriodPeriodEntityRegionReporting Status
Tier13531.32153P6 - Jun-23P6UKRUKOpening ARR
Tier13531.32153P6 - Jun-23P6UKRUKGross Retention
Tier1256.81846P6 - Jun-23P6UKRUKUpsell
Tier13788.13999P6 - Jun-23P6UKRUKNet Retention
Tier110P6 - Jun-23P6UKRUKAcquisitions
Tier13788.13999P6 - Jun-23P6UKRUKClosing ARR
Tier10200P6 - Jun-23P6UKRUKChurn
Tier10100P6 - Jun-23P6UKRUKDownsell
Tier1050P6 - Jun-23P6UKRUKNew
  • coetseem's avatar
    coetseem
    Frequent Visitor
    2 years ago
    StatusValueDAX %
    Opening ARR3531.32100.0%
    Churn200.005.7%
    Downsell100.002.8%
    Gross Retention3531.32100.0%
    Upsell256.827.3%
    Net Retention3788.14107.3%
    New50.001.4%
    Acquisitions10.000.3%
    Closing ARR3788.14107.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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      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's avatar
        coetseem
        Frequent Visitor
        2 years ago

        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.