Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi everybody,
I have a table that is grouped by "Distribuidores" and "Anunciantes". The meausures of the table are "Facturado Current Year" and "Facturado Last Year". I want to know the percent of "Anunciantes" that were lost and earned over the year. So, for the "Anunciantes " lost I need to calculate the "Anunciantes" that were in LY but they are not in the current year. And for the anunciantes earned I need to calculate the "Anunciantes" that are in the current year but not on the last year.
The table is below. I made a flag that is 1 when the "Anunciante" are in LY but aren't in the current year. For the "Anunciantes" earned I mad the opposite.
Flag_1 = IF(NOT(ISBLANK([Facturado LY]));IF([Facturado LY]>0 && ISBLANK([Facturado]);1))
Flag_2= IF(NOT(ISBLANK([Facturado]));IF([Facturado]>0 && isblank([Facturado LY]);1))
My idea is sum the flags and divide by the total of rows. The problem is that I can't sum up a meausure or I don't know how to do it. Can you help please?
I exported the table. Var is Flag_1 and var3 is Flag_2 https://drive.google.com/file/d/1866-h9C99vdXrYqyeSrLhnlFGZsXSG4n/view?usp=sharing
Hi @Anonymous
will this code give you what you need?
percentage new = DIVIDE ( CALCULATE ( DISTINCTCOUNT ( data[Anunciante] ); FILTER ( data; OR ( ISBLANK ( data[Facturado LY] ); data[Facturado LY] <= 0 ) && NOT ( ISBLANK ( ( data[Facturado] ) ) && data[Facturado] > 0 ) ) ); CALCULATE ( DISTINCTCOUNT ( data[Anunciante] ); FILTER ( data; NOT ( ISBLANK ( data[Facturado] ) ) && data[Facturado] > 0 ) ) )
I have reused some of the code you had put into the flags
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |