Nível: intermediário
Como leitores regulares do meu blog sabem, Gosto de ajudar as pessoas nos fóruns que precisam de ajuda com DAX, Power Query o Power BI. Recentemente, respondi a algumas perguntas para pessoas que poderiam se beneficiar de uma solução DAX “Banding” e, portanto, Decidi escrever esta postagem como um link permanente para o tópico. Aprendi sobre a técnica de bandagem DAX de Alberto Ferrari em http://sqlbi.com
O que são bandas?
El agrupamiento es una técnica que le permite “agrupar” o “estratificar” sus datos en una tabla en “bandas” (às vezes chamada de análise de coorte). Digamos que você tenha uma mesa de clientes como esta (mostrado abaixo). Em meu banco de dados de amostra, tenho 18.000 clientes e eu sei a idade de cada um.
Isso fornece a oportunidade de analisar os dados de vendas com base na idade para que para descobrir se pessoas de diferentes idades compram de maneira diferente. Abaixo está uma tabela dinâmica ilustrando o que é possível com os dados. Mas o problema é que existem muitos detalhes para serem úteis.
Eu realmente não quero segmentar clientes de 35 anos de forma diferente dos clientes de 36 anos. Idealmente, Eu gostaria de agrupar (o estratificar) a los clientes en “bandas” para poder tratar a un grupo de clientes similares de la misma manera. Por isso, você pode querer agrupar meus clientes assim.
Depois de agrupar os clientes por idade, Posso criar uma tabela dinâmica como a mostrada abaixo.
Observe que é muito mais fácil encontrar informações sobre os dados quando há menos detalhes. Posso ver imediatamente na tabela dinâmica acima que conforme as pessoas envelhecem, tendem a se interessar mais por bicicletas de montanha e turismo e menos por bicicletas de estrada (claro, este é um banco de dados falso, então as ideias não são necessariamente realistas). .
Você precisará de uma coluna calculada para as bandas
Eu sou um forte defensor do uso de medições (sem colunas calculadas) sempre que possível. Você pode ler mais sobre isso na minha Base de Conhecimento aqui. Mas neste caso, uma medida não funcionará. As medidas só podem ser usadas na seção de valores de uma tabela dinâmica – no puede usarlos en las áreas de “filtrado” como Slicers, Linhas, Colunas e Filtros. Na tabela dinâmica acima, as bandas são usadas para dividir a tabela dinâmica em linhas e, portanto, uma medida não vai funcionar.
Você poderia escrever uma declaração If
Então o objetivo é criar uma coluna calculada na tabela do cliente que agrupa as pessoas de acordo com a idade em uma faixa estratificada. Qualquer usuário do Excel saberia que você pode escrever uma declaração IF para isso (DAX tem sintaxe muito semelhante para a instrução IF no Excel normal). La sintaxis de DAX para una sola instrucción IF es la siguiente
IF Quádruplo(Clientes[Era] <= 18, "18 and Under", "Over 18")
La columna calculada de DAX anterior devolverá uno de los dos valores posibles según la edad del cliente. La complejidad surge cuando desea tener múltiples condiciones. Como mencioné anteriormente en esta publicación, existem 6 grupos posibles que tengo en mente (que se muestran aquí nuevamente).
Para hacer esto con declaraciones IF, necesitaría anidar múltiples declaraciones IF entre sí de la siguiente manera.
= IF( Clientes[Era] <= 18, "18 and Under", IF Quádruplo( Clientes[Era] > 18 && Clientes[Era] <= 30, ">18 e <=30", IF Quádruplo( Clientes[Era] > 30 && Clientes[Era] <= 40, ">30 e <=40", IF Quádruplo( Clientes[Era] > 40 && Clientes[Era] <= 50, ">40 e <= 50", IF Quádruplo(Clientes[Era] > 50 && Clientes[Era] <= 60, ">50 e <=60", ">60" ) ) ) ) )
Você vê o problema? Esta coluna calculada funciona, mas é muito difícil escrever, ler e modificar. E o que é mais importante, Há um caminho melhor: usar técnica de bandagem.
Processo de criação de bandas
A melhor maneira envolve o seguinte processo.
- Crie uma tabela no Excel contendo os nomes dos grupos e os limites de idade inferiores / superior
- Carregue a mesa no Power Pivot
- Não conecte a mesa a qualquer outra mesa, é uma mesa desconectada
- Escreva uma coluna calculada DAX que compare a idade de cada cliente com os limites inferiores / topo em sua tabela desconectada e retornar a única linha da tabela de banda que corresponde a cada cliente.
Aqui está o detalhe de como fazer isso.
Crie uma tabela no Excel
Esta é a aparência de uma tabela no Excel.
Os principais recursos a serem considerados são
- Existe uma coluna de ID: isso será usado para classificar a coluna Banda mais tarde
- Una columna de “Banda”: este é o rótulo que descreve cada grupo. Observe que os grupos são mutuamente exclusivos e coletivamente exaustivos (MECE).
- Hay una columna “a partir de” e “até” que establece los límites inferior y superior de cada grupo de edad. Observe que a idade superior de um grupo coincidirá com a idade inferior do próximo grupo. Usei o DAX para garantir que não haja sobreposição na coluna calculada.
Escreva uma coluna calculada
O objetivo da coluna computada é filtrar a tabela da banda desconectada para que 1 e só 1 fila sea “visível” o “sin filtrar” para cada cliente. Esta é a fórmula
= CALCULAR(
VALORES(AgeBands[Banda]),
FILTRO(AgeBands,
Clientes[Era] > AgeBands[A partir de] &&
Clientes[Era] <= AgeBands[Para]
)
)
O papel de a porção FILTER desta fórmula é filtrar a tabela AgeBands para apenas uma linha não é filtrada. O que o FILTER faz é aplicar 2 reglas de filtro para garantizar que el cliente sea mayor que la columna “a partir de” e “menor o igual” que la columna “até”. É essa porção menor ou igual àquela que garante que não haja travessias de indivíduos em grupos múltiplos.. (Mutualmente exclusivo).
A função VALUES tem uma capacidade única. VALUES retorna uma tabela de coluna única. Se essa tabela também tiver uma única linha, VALUES converte a tabela em um valor escalar e permite que esse valor seja usado como resultado em uma coluna calculada ou de fato em uma célula da tabela dinâmica. Uma vez que a fórmula garante que 1 e só 1 linha não filtrada nesta tabela de banda, então VALUES será ativado, extrair tag para grupo de AgeBands[Banda] coluna e coloque esse valor na coluna calculada.
Classifique a nova coluna calculada
Há mais um problema para resolver: os nomes dos novos grupos não estão classificados corretamente na tabela dinâmica. Por padrão, as colunas de dados serão classificadas em ordem alfanumérica. Em seguida, os rótulos serão classificados assim:
Para resolver este problema, Eu criei uma segunda coluna calculada que traz a id de classificação para a tabela de clientes. É simples fazer isso – simplemente copie la fórmula en la primera columna calculada y luego actualice la porción de VALORES con la columna “EU IRIA” da seguinte maneira.
= CALCULAR( VALORES(AgeBands[EU IRIA]), FILTRO(AgeBands, Clientes[Era] > AgeBands[A partir de] && Clientes[Era] <= AgeBands[Para]) )
Uma vez que esta segunda coluna calculada foi criada, é possível dizer ao Power Pivot para usar a nova coluna de valores de id de classificação como a ordem de classificação para a coluna de faixa etária (mostrado abaixo).
Compressão de colunas calculadas
Como eu mencionei antes, Não sou um grande fã de colunas calculadas, exceto onde eles são realmente necessários. Existem algumas diretrizes sobre quando é correto usar uma coluna calculada; você pode ler sobre todos eles no link que forneci para minha Base de Conhecimento anteriormente. Porém, os pontos-chave a ter em mente aqui são.
- Essas colunas calculadas estão na tabela de pesquisa (Clientes). Colunas calculadas em tabelas de pesquisa geralmente são boas.
- As colunas calculadas têm baixa cardinalidade (quer dizer, um baixo número de valores únicos). Em geral, isso está bem também, mesmo em uma grande tabela de dados, se necessário.
Agora, para usar a nova coluna calculada
Ahora que la tabla Clientes tiene esta nueva columna de “Banda de edad”, esta coluna pode ser usada em visualizações. Pode ser usado em linhas, colunas, filtros e segmentações. Aqui está um exemplo de linhas:
E um exemplo de cortador:
Aprenda a escrever DAX
El tema de las bandas es solo una de las muchas técnicas que cubro en mis libros “Supercarga Power BI” e “Excel Supercharge“. Se você é um usuário do Power BI ou do Excel e está aprendendo a usar DAX, meus livros fornecem uma cobertura abrangente de todos os tópicos de que você precisa para começar sua jornada como um ninja Power Pivot.
