Forum Discussion

ZuzanaK's avatar
ZuzanaK
Frequent Visitor
3 years ago
Solved

% Movement

Hello,

 

I am trying to create a % movement year on year as on the table below.

 

 

The formula from the table is 37% in 2022 is (283-207)/207 = 37%

I have created one measure as below

client_count_current =
CALCULATE (DISTINCTCOUNT(uk_live_campaign[client_name]),
     FILTER (
         ALLSELECTED (calendar_main),
        calendar_main[calendarYear]
             = MAX ('calendar_main'[calendarYear])
     )
 )
 
and another one for previous year
 
client_count_prev_yr =
CALCULATE (DISTINCTCOUNT(uk_live_campaign[client_name]),
     FILTER (
         ALLSELECTED (calendar_main),
        calendar_main[calendarYear]
             = MAX ('calendar_main'[calendarYear]) -1
     )
 )
 
In order to get % movement, I have created measure with the following formula
 
%Movement_client = CALCULATE(SUMx('Key Measures','Key Measures'[client_count_current]) - SUMX('Key Measures','Key Measures'[client_count_prev_yr])) / SUMX('Key Measures','Key Measures'[client_count_prev_yr]) *100
 
 
Unfortunately, when I try to display it, it keeps showing the below error.
 

Could you please help?

 

Thanks, 

Zuzana

  • v-zhangti's avatar
    v-zhangti
    3 years ago

    Hi, ZuzanaK 

     

    You can try the following methods. [client_count_current] and [client_count_prev_yr] are measures.

    %Movement_client = DIVIDE( [client_count_current]-[client_count_prev_yr],[client_count_prev_yr])

    Is this the result you expect?

     

    Best Regards,

    Community Support Team _Charlotte

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

2 Replies

  • v-zhangti's avatar
    v-zhangti
    Community Support
    3 years ago

    Hi, ZuzanaK 

     

    You can try the following methods. [client_count_current] and [client_count_prev_yr] are measures.

    %Movement_client = DIVIDE( [client_count_current]-[client_count_prev_yr],[client_count_prev_yr])

    Is this the result you expect?

     

    Best Regards,

    Community Support Team _Charlotte

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

    • ZuzanaK's avatar
      ZuzanaK
      Frequent Visitor
      3 years ago

      Hello, 

       

      I have used that formula but it still showed the same error. I have changed the source for year and then it worked. 

       

      Thanks, 

      Zuzana