Zwykły Excelowy Pivot – NIE. Ale już tabela przestawna zbudowana na modelu danych z wykorzystaniem dodatku Power Pivot (który jest już dostępny w każdym Excelu 365 i 2016) – TAK. Analogicznie możemy pracować z wartościami zwracanymi przez miary w Power BI. Jak uzyskać taki efekt?

Jaki region obsługuje klienta?

Zacznijmy od przygotowania danych. Interesuje nas uzyskanie w jednej komórce wartości tabel przestawnej informacji o regionie, który obsługuje klienta. W zwykłej tabeli przestawnej pole Region wrzucone na półkę wartości może pokazać tylko agregaty matematyczne – sumę, średnią, licznik itp.

Co więcej, w zwykłej tabeli przestawnej nie możemy nawet policzyć unikatowych Regionów, które obsługują Klienta (chyba że znamy sposób z 1/LICZ.JEŻELI omówiony w kursie Tabele przestawne Excel w Akademii SkuteczneRaporty.pl).

Przeładowanie danych przez model danych

Zwykle raport rozpoczynam od czystego pliku Excel i Power Query, ale na potrzeby tego artykułu dodam na szybko dane do modelu danych, wstawiając tabelę przestawną.

Modelu danych nie musimy budować. Wystarczy, że trzymamy tam dane.

Ile regionów obsługuje klienta?

Mając dane w modelu, możemy wstawić na nim tabelę przestawną i przypomnieć sobie jedyną nowość w interfejsie tabeli przestawnej wspieranej przez Power Pivot – DISTNICTCOUNT = Wartość odrębna = unikatowe regiony.

Jaki region obsługuje klienta?

Przy założeniu, że jest to dokładnie 1 region, możemy zbudować miarę, która pokaże przewagę tabeli przestawnej z Power Pivot vs zwykła: wyświetlanie tekstu na półce wartości.

Zacznijmy od stworzenia miary w języku DAX, np. z karty Power Pivot

Moja miara przyjmuje postać:

Nazwa regionu := IF(HASONEVALUE(Tabela1[Region]);VALUES(Tabela1[Region]);"Wiele")

W skrócie:

– jeśli jest dokładnie 1 wartość, tabela przestawna może ją wyświetlić w VALUES – stąd test HASONEVALUE

– jeśli >1 wtedy wyświetl tekst wiele

Jakie regiony obsługują klienta?

To nie koniec. DAX pozwala na pracę z różnymi kalkulacjami w pamięci, także z tymi związanymi z łączeniem tekstów. Z pomocą przychodzi formuła:

Nazwy regionów := CONCATENATEX(VALUES(Tabela1[Region]);Tabela1[Region];";")

W skrócie: złącz teksty ze wszystkich unikatowych wartości regionów (stąd VALUES) i oddziel je średnikiem.

Co jeśli regionów jest dużo?

Miara będzie nieefektywna. Wtedy lepiej ograniczyć ją do np. 3 pierwszych. Oto przykład dla 5 pierwszych produktów kupionych przez klienta.

Miara w przykładowej tabeli ma postać:

Pierwszych 5 produktów := VAR __DISTINCT_VALUES_COUNT = DISTINCTCOUNT(Tabela1[Nazwa Produktu])
VAR __MAX_VALUES_TO_SHOW = 5
RETURN
               IF(
                              __DISTINCT_VALUES_COUNT > __MAX_VALUES_TO_SHOW;
                              CONCATENATE(
                                            CONCATENATEX(
                                                           TOPN(
                                                                          __MAX_VALUES_TO_SHOW;
                                                                          VALUES(Tabela1[Nazwa Produktu]);
                                                                          Tabela1[Nazwa Produktu];
                                                                          ASC
                                                           );
                                                           Tabela1[Nazwa Produktu];
                                                           ", ";
                                                           Tabela1[Nazwa Produktu];
                                                           ASC
                                            );
                                            ", etc."
                              );
                              CONCATENATEX(
                                            VALUES(Tabela1[Nazwa Produktu]);
                                            Tabela1[Nazwa Produktu];
                                            ", ";
                                            Tabela1[Nazwa Produktu];
                                            ASC
                              )
               )

Plik do pobrania

Tu możesz pobrać plik Excel z modelem danych i miarami DAX, w których tabela przestawna wyświetla teksty.

PS. Zauważyliście, że ikona Excel na pasku zadań Windowsa się zmieniła? Idą zmiany 😉