Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jvilchez
New Member

Need Help EARLIER

Greetings,
I have the following query, I have a table that contains 7500 registers approx; And what I want to do is a measure that helps me show the percentage of user participation per year.

Likewise, every year the users increase; Example in 2014 there were 89 users, in 2015 1500 users and by 2016 there were 2215 users.

But the participation of the mentioned years is the following 2014: 32; 2015: 152; And 2016: 350.

The percentage of participation would be for 2014 = (32/89), 2015 = (152 / (1500 + 89)) and 2016 = (350 / (2215 + 1500 + 89)).
Use the function earlier, and get it counted and users increase by year:

CALCULATE(COUNT('vmc_subastas_consultas DIM_USUARIO'[ID_USUARIO]);FILTER('vmc_subastas_consultas DIM_USUARIO';'vmc_subastas_consultas DIM_USUARIO'[FECHA_REGISTRO].[Año]<=EARLIER('vmc_subastas_consultas DIM_USUARIO'[FECHA_REGISTRO].[Año]));FILTER('vmc_subastas_consultas DIM_USUARIO';'vmc_subastas_consultas DIM_USUARIO'[ESTADO]="ACTIVO");FILTER('vmc_subastas_consultas DIM_USUARIO';'vmc_subastas_consultas DIM_USUARIO'[DEMO]=0);FILTER('vmc_subastas_consultas DIM_USUARIO';'vmc_subastas_consultas DIM_USUARIO'[COD_CLI]=0))

And this is repeated in all my records; And when I show it I get the following:

Captura1.PNG

 

 


Does not give me the information I want and adds the records with the values ​​of the years, and can only give me the result when I change the option to averagCaptura2.PNG

 

 

 

 


And generating my measure only gives me other values ​​that are not mentioned in the above lines. Some help?
Thanks..... 
1 ACCEPTED SOLUTION
v-sihou-msft
Employee
Employee

@jvilchez

 

In this scenario, to calculate the percentage of participation every year, you just need to use the current year participation divided by cumulative users. Please refer to measure below:

 

Participation Rate = SUM([Participation])/CALCULATE(SUM(Table1[Users]),FILTER(ALL(Table1[Year]),Table1[Year]<=MAX(Table1[Year])))

 

5555.PNG

 

Regards,

View solution in original post

1 REPLY 1
v-sihou-msft
Employee
Employee

@jvilchez

 

In this scenario, to calculate the percentage of participation every year, you just need to use the current year participation divided by cumulative users. Please refer to measure below:

 

Participation Rate = SUM([Participation])/CALCULATE(SUM(Table1[Users]),FILTER(ALL(Table1[Year]),Table1[Year]<=MAX(Table1[Year])))

 

5555.PNG

 

Regards,

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.