User Profile
mlozano
Helper III
Joined 4 years ago
User Widgets
Contributions
Problema RANK Condicionado
Estoy intentando calcular la clasificación de una agencia usando la fórmula RANKX, pero no puedo resolverlo. Lo que estoy intentando hacer es que el ranking se calcule teniendo en cuenta la inversión de cada agencia, pero esto no puede considerar agencias con YoY negativo, hay que excluirlas del ranking. Solo quiero un ranking de Agencias que tengan una variación % positiva (2023 vs 2024). Estoy usando la siguiente fórmula pero sin mucho éxito. RANKX Agencia 1 Pos = VAR InversionYTD = [Inversión neta YTD Agencia] VAR VariacionYTD = [Variación YTD Agencia] RETURN IF( NOT ISBLANK(InversionYTD) && InversionYTD <> 0 && VariacionYTD > 0, RANKX( FILTER( ALLSELECTED('Industria'[Agencia]), [Variación YTD Agencia] > 0 ), CALCULATE( [Inversión neta YTD Agencia], REMOVEFILTERS('Industria'[Anunciante]), -- Delete filters of advertiser column ), , DESC, Dense ), BLANK() ) El resultado esperado sería un objeto visual matricial como el de la imagen. Resultado esperado Anunciante Agencia 2023 2024 Diff YoY Rankx TECNOQUIMICAS SANCHO/BBDO $ 364,529 $ 435,109 $ 70,580 19% 1 CENTRAL CERVECERA SANCHO/BBDO $ 180 $ 32,094 $ 31,915 17744% 1 PEPSI COL+POSTOBON SANCHO/BBDO $ 3,856 $ 20,385 $ 16,529 429% 1 ALMACENES EXITO SANCHO/BBDO $ 65,194 $ 79,979 $ 14,785 23% 1 BANCOLOMBIA SANCHO/BBDO $ 45,218 $ 59,528 $ 14,309 32% 1 ALIMENTOS POLAR SANCHO/BBDO $ 14,610 $ 26,580 $ 11,970 82% 1 MERCADOLIBRE.COM SANCHO/BBDO $ 14,966 $ 24,951 $ 9,986 67% 1 PEPSICO ALIMENTOS SANCHO/BBDO $ 11,078 $ 17,517 $ 6,439 58% 1 ECOPETROL SANCHO/BBDO $ 8,340 $ 12,604 $ 4,264 51% 1 LEADERS+CEET SANCHO/BBDO $ 2,234 $ 5,461 $ 3,226 144% 1 C FUNCIONAL BEBIDAS SANCHO/BBDO $ 965 $ 3,937 $ 2,972 308% 1 DAIMLER COLOMBIA SA SANCHO/BBDO $ 2,167 $ 5,033 $ 2,865 132% 1 TERPEL SANCHO/BBDO $ 1,763 $ 4,196 $ 2,433 138% 1 AVON CALLINGS SANCHO/BBDO $ 268 $ 1,493 $ 1,225 456% 1 ARTURO CALLE SANCHO/BBDO $ 301 $ 1,380 $ 1,079 359% 1 CORONA SANCHO/BBDO $ 2,397 $ 2,782 $ 385 16% 1 L&C S.A.S SANCHO/BBDO $ 892 $ 1,238 $ 346 39% 1 PJ COL SAS SANCHO/BBDO $ 478 $ 812 $ 334 70% 1 ALM EXITO+B COLPAT SANCHO/BBDO $ 83 $ 230 $ 147 176% 1 COLGATE PALMOLIVE YOUNG & RUBICAM $ 159,954 $ 257,348 $ 97,394 61% 2 COLGATE PALMOLIV INT YOUNG & RUBICAM $ 9,363 $ 31,787 $ 22,424 240% 2 HUAWEI TECHNOLOGIES YOUNG & RUBICAM $ 541 $ 3,313 $ 2,772 512% 2 DERCO SA YOUNG & RUBICAM $ 3,175 $ 4,147 $ 972 31% 2 WALT DISNEY COMPANY BTL $ 145,513 $ 254,319 $ 108,806 75% 3 Sample Data1.xlsxRe: RANKX Returning All as 1
But in case you need to further condition the range I'm trying to calculate an agency's ranking using the RANKX formula, but I can't figure it out. What I am trying to do is for the ranking to be calculated taking into account the investment of each agency, but this cannot consider agencies with negative YoY, they must be excluded from the ranking. I only want a ranking of Agencies that have a positive % variation (2023 vs 2024). I am using the following formula but without much success. RANKX Agencia 1 Pos = VAR InversionYTD = [Inversión neta YTD Agencia] VAR VariacionYTD = [Variación YTD Agencia] RETURN IF( NOT ISBLANK(InversionYTD) && InversionYTD <> 0 && VariacionYTD > 0, RANKX( FILTER( ALLSELECTED('Industria'[Agencia]), [Variación YTD Agencia] > 0 ), CALCULATE( [Inversión neta YTD Agencia], REMOVEFILTERS('Industria'[Anunciante]), -- Delete filters of advertiser column ), , DESC, Dense ), BLANK() ) The expected result would be a matrix visual object like the one in the image. Expected result Anunciante Agencia 2023 2024 Diff YoY Rankx TECNOQUIMICAS SANCHO/BBDO $ 364,529 $ 435,109 $ 70,580 19% 1 CENTRAL CERVECERA SANCHO/BBDO $ 180 $ 32,094 $ 31,915 17744% 1 PEPSI COL+POSTOBON SANCHO/BBDO $ 3,856 $ 20,385 $ 16,529 429% 1 ALMACENES EXITO SANCHO/BBDO $ 65,194 $ 79,979 $ 14,785 23% 1 BANCOLOMBIA SANCHO/BBDO $ 45,218 $ 59,528 $ 14,309 32% 1 ALIMENTOS POLAR SANCHO/BBDO $ 14,610 $ 26,580 $ 11,970 82% 1 MERCADOLIBRE.COM SANCHO/BBDO $ 14,966 $ 24,951 $ 9,986 67% 1 PEPSICO ALIMENTOS SANCHO/BBDO $ 11,078 $ 17,517 $ 6,439 58% 1 ECOPETROL SANCHO/BBDO $ 8,340 $ 12,604 $ 4,264 51% 1 LEADERS+CEET SANCHO/BBDO $ 2,234 $ 5,461 $ 3,226 144% 1 C FUNCIONAL BEBIDAS SANCHO/BBDO $ 965 $ 3,937 $ 2,972 308% 1 DAIMLER COLOMBIA SA SANCHO/BBDO $ 2,167 $ 5,033 $ 2,865 132% 1 TERPEL SANCHO/BBDO $ 1,763 $ 4,196 $ 2,433 138% 1 AVON CALLINGS SANCHO/BBDO $ 268 $ 1,493 $ 1,225 456% 1 ARTURO CALLE SANCHO/BBDO $ 301 $ 1,380 $ 1,079 359% 1 CORONA SANCHO/BBDO $ 2,397 $ 2,782 $ 385 16% 1 L&C S.A.S SANCHO/BBDO $ 892 $ 1,238 $ 346 39% 1 PJ COL SAS SANCHO/BBDO $ 478 $ 812 $ 334 70% 1 ALM EXITO+B COLPAT SANCHO/BBDO $ 83 $ 230 $ 147 176% 1 COLGATE PALMOLIVE YOUNG & RUBICAM $ 159,954 $ 257,348 $ 97,394 61% 2 COLGATE PALMOLIV INT YOUNG & RUBICAM $ 9,363 $ 31,787 $ 22,424 240% 2 HUAWEI TECHNOLOGIES YOUNG & RUBICAM $ 541 $ 3,313 $ 2,772 512% 2 DERCO SA YOUNG & RUBICAM $ 3,175 $ 4,147 $ 972 31% 2 WALT DISNEY COMPANY BTL $ 145,513 $ 254,319 $ 108,806 75% 3 I have the following tableRANKX with additional conditions
I'm trying to calculate an agency's ranking using the RANKX formula, but I can't figure it out. What I am trying to do is for the ranking to be calculated taking into account the investment of each agency, but this cannot consider agencies with negative YoY, they must be excluded from the ranking. I only want a ranking of Agencies that have a positive % variation (2023 vs 2024). I am using the following formula but without much success. RANKX Agencia 1 Pos = VAR InversionYTD = [Inversión neta YTD Agencia] VAR VariacionYTD = [Variación YTD Agencia] RETURN IF( NOT ISBLANK(InversionYTD) && InversionYTD <> 0 && VariacionYTD > 0, RANKX( FILTER( ALLSELECTED('Industria'[Agencia]), [Variación YTD Agencia] > 0 ), CALCULATE( [Inversión neta YTD Agencia], REMOVEFILTERS('Industria'[Anunciante]), -- Delete filters of advertiser column ), , DESC, Dense ), BLANK() ) The expected result would be a matrix visual object like the one in the image. Expected result Anunciante Agencia 2023 2024 Diff YoY Rankx TECNOQUIMICAS SANCHO/BBDO $ 364,529 $ 435,109 $ 70,580 19% 1 CENTRAL CERVECERA SANCHO/BBDO $ 180 $ 32,094 $ 31,915 17744% 1 PEPSI COL+POSTOBON SANCHO/BBDO $ 3,856 $ 20,385 $ 16,529 429% 1 ALMACENES EXITO SANCHO/BBDO $ 65,194 $ 79,979 $ 14,785 23% 1 BANCOLOMBIA SANCHO/BBDO $ 45,218 $ 59,528 $ 14,309 32% 1 ALIMENTOS POLAR SANCHO/BBDO $ 14,610 $ 26,580 $ 11,970 82% 1 MERCADOLIBRE.COM SANCHO/BBDO $ 14,966 $ 24,951 $ 9,986 67% 1 PEPSICO ALIMENTOS SANCHO/BBDO $ 11,078 $ 17,517 $ 6,439 58% 1 ECOPETROL SANCHO/BBDO $ 8,340 $ 12,604 $ 4,264 51% 1 LEADERS+CEET SANCHO/BBDO $ 2,234 $ 5,461 $ 3,226 144% 1 C FUNCIONAL BEBIDAS SANCHO/BBDO $ 965 $ 3,937 $ 2,972 308% 1 DAIMLER COLOMBIA SA SANCHO/BBDO $ 2,167 $ 5,033 $ 2,865 132% 1 TERPEL SANCHO/BBDO $ 1,763 $ 4,196 $ 2,433 138% 1 AVON CALLINGS SANCHO/BBDO $ 268 $ 1,493 $ 1,225 456% 1 ARTURO CALLE SANCHO/BBDO $ 301 $ 1,380 $ 1,079 359% 1 CORONA SANCHO/BBDO $ 2,397 $ 2,782 $ 385 16% 1 L&C S.A.S SANCHO/BBDO $ 892 $ 1,238 $ 346 39% 1 PJ COL SAS SANCHO/BBDO $ 478 $ 812 $ 334 70% 1 ALM EXITO+B COLPAT SANCHO/BBDO $ 83 $ 230 $ 147 176% 1 COLGATE PALMOLIVE YOUNG & RUBICAM $ 159,954 $ 257,348 $ 97,394 61% 2 COLGATE PALMOLIV INT YOUNG & RUBICAM $ 9,363 $ 31,787 $ 22,424 240% 2 HUAWEI TECHNOLOGIES YOUNG & RUBICAM $ 541 $ 3,313 $ 2,772 512% 2 DERCO SA YOUNG & RUBICAM $ 3,175 $ 4,147 $ 972 31% 2 WALT DISNEY COMPANY BTL $ 145,513 $ 254,319 $ 108,806 75% 3 I have the following tableRe: DAX CALCULATE ERROR
Thanks for your answer. It is not what I need, the participation percentage is already calculated, which is the Share % SKU ACT Volume measure, now what I have to do is, that measure, which is my participation percentage, include it in a calculate that groups it by month and category . So if you can see with the following code: SUM of Share % SKU ACT Volume by category Static = CALCULATE(Worksheet[Share % SKU ACT Volume], ALLEXCEPT(Worksheet, Worksheet[Month], Worksheet[Category])) Result Without filters It has the result that I expect, what happens is that this result is not dynamic since I apply the region filter and it is the same all the time, what I need is to get that result and that it fits to the granularity defined by the filters. With filters The expected result would be the distribution of 100% that represents the total of my participation among my four categories and that this be dynamic every time I change the region filter.
