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