Scenariji DAX v orodju PowerPivot

Velja za
Excel za Microsoft 365 Excel 2024 Excel 2021 Excel 2019 Excel 2016

V tem razdelku so povezave do primerov, ki prikazujejo uporabo formul DAX v teh scenarijih.

  • Izvajanje zapletenih izračunov
  • Delo z besedilom in datumi
  • Pogojne vrednosti in preizkušanje napak
  • Uporaba časovne inteligence
  • Razvrščanje in primerjava vrednosti

V tem članku

Začetek

Obiščite wiki središča za vire DAX , kjer lahko najdete najrazličnejše informacije o jeziku DAX, vključno z blogi, vzorci, informativnimi knjigami in videoposnetki, ki so jih zagotovili vodilni strokovnjaki v panogi in Microsoft.

Scenariji: Izvajanje zapletenih izračunov

Formule DAX lahko izvajajo zapletene izračune, ki vključujejo združevanje po meri, filtriranje in uporabo pogojnih vrednosti. V tem razdelku so primeri, kako začeti uporabljati izračune po meri.

Ustvarjanje izračunov po meri za vrtilno tabelo

CALCULATE in CALCULATETABLE sta zmogljivi in prilagodljivi funkciji, ki sta uporabni za določanje izračunanih polj. S temi funkcijami lahko spremenite kontekst, v katerem bo izračun izveden. Prilagodite lahko tudi vrsto združevanja ali matematične operacije, ki jo želite izvesti. Za primere si oglejte te teme.

Uporaba filtra za formulo

Na večini mest, kjer funkcija jezika DAX vzame tabelo kot argument, lahko običajno posredujete filtrirano tabelo, bodisi s funkcijo FILTER namesto imena tabele ali tako, da določite izraz filtra kot enega od argumentov funkcije. V spodnjih temah so primeri, kako ustvariti filtre in kako filtri vplivajo na rezultate formul. Če želite več informacij, glejte Filtriranje podatkov v formulah jezika DAX.

Funkcija FILTER vam omogoča, da določite pogoje filtra z izrazom, medtem ko so druge funkcije zasnovane posebej za filtriranje praznih vrednosti.

Selektivno odstranjevanje filtrov za ustvarjanje dinamičnega razmerja

Z ustvarjanjem dinamičnih filtrov v formulah lahko preprosto odgovarjate na vprašanja, kot so ta:

  • Kakšen je bil prispevek prodaje trenutnega izdelka k skupni prodaji v tem letu?
  • Koliko je ta oddelek prispeval k skupnemu dobičku za vsa poslovna leta v primerjavi z drugimi oddelki?

Na formule, ki jih uporabljate v vrtilni tabeli, lahko vpliva kontekst vrtilne tabele, vendar lahko kontekst selektivno spremenite tako, da dodate ali odstranite filtre. Primer v temi ALL prikazuje, kako to narediti. Če želite poiskati razmerje med prodajo za določenega prodajalca in prodajo za vse prodajalce, ustvarite mero, ki izračuna vrednost za trenutni kontekst, deljeno z vrednostjo za kontekst ALL.

V temi ALLEXCEPT je primer, kako selektivno počistiti filtre v formuli. Oba primera vas vodita skozi spremembo rezultatov glede na zasnovo vrtilne tabele.

Če želite druge primere izračuna razmerij in odstotkov, si oglejte te teme:

Uporaba vrednosti iz zunanje zanke

Poleg uporabe vrednosti iz trenutnega konteksta v izračunih lahko DAX uporabi vrednost iz prejšnje zanke pri ustvarjanju nabora sorodnih izračunov. V spodnji temi je navodilo za ustvarjanje formule, ki se sklicuje na vrednost iz zunanje zanke. Funkcija EARLIER podpira do dve ravni ugnezdenih zank.

Če želite izvedeti več o kontekstu vrstice in sorodnih tabelah ter o uporabi tega koncepta v formulah, glejte Kontekst v formulah DAX.

Scenariji: Delo z besedilom in datumi

V tem razdelku so povezave do referenčnih tem jezika DAX, ki vsebujejo primere pogostih scenarijev, ki vključujejo delo z besedilom, ekstrahiranje in sestavljanje vrednosti datuma in ure ali ustvarjanje vrednosti na podlagi pogoja.

Ustvarjanje ključnega stolpca s povezovanjem

Power Pivot ne dovoljuje sestavljenih tipk; Če imate sestavljene ključe v viru podatkov, jih boste morda morali združiti v en stolpec s ključem. V tej temi je primer, kako ustvarite izračunani stolpec na podlagi sestavljenega ključa.

Compose datuma na podlagi delov datuma, izvlečenih iz besedilnega datuma

Power Pivot za delo z datumi uporablja podatkovni tip datuma/ure strežnika SQL Server; če zunanji podatki vsebujejo datume, ki so drugače oblikovani – na primer če so datumi zapisani v podregionalni obliki zapisa datuma, ki je podatkovni mehanizem Power Pivot ne prepozna, ali če vaši podatki uporabljajo nadomestne ključe celega števila – boste morda morali uporabiti formulo DAX za ekstrahiranje datumov delov in nato sestaviti dele v veljaven datum. časovna reprezentacija.

Če imate na primer stolpec z datumi, ki so bili predstavljeni kot celo število in nato uvoženi kot besedilni niz, lahko niz pretvorite v vrednost datuma/časa s to formulo:

=DATUM(DESNO([Vrednost1];4);LEVO([Vrednost1];2);SREDINA([Vrednost1];2))

vrednost1 Rezultat
01032009 1/3/2009
12132008 12/13/2008
06252007 6/25/2007

V spodnjih temah je več informacij o funkcijah, ki se uporabljajo za ekstrahiranje in sestavljanje datumov.

Določanje oblike zapisa datuma ali števila po meri

Če podatki vsebujejo datume ali številke, ki niso predstavljene v eni od standardnih oblik besedila sistema Windows, lahko določite obliko zapisa po meri, da zagotovite pravilno obdelavo vrednosti. Te oblike zapisa se uporabljajo pri pretvarjanju vrednosti v nize ali iz nizov. V spodnjih temah je tudi podroben seznam vnaprej določenih oblik zapisa, ki so na voljo za delo z datumi in številkami.

Spreminjanje podatkovnih tipov s formulo

V dodatku Power Pivot je podatkovni tip izhoda določen z izvornimi stolpci in podatkovnega tipa rezultata ne morete izrecno določiti, ker optimalni podatkovni tip določa Power Pivot. Vendar pa lahko uporabite implicitne pretvorbe podatkovnih tipov, ki jih izvede Power Pivot, da spremenite izhodni podatkovni tip. 

  • Če želite pretvoriti datum ali številski niz v število, pomnožite z 1,0. Naslednja formula na primer izračuna trenutni datum minus 3 dni in nato izpiše ustrezno celo število.
    =(DANES()-3)*1.0
  • Če želite pretvoriti datum, številko ali vrednost valute v niz, združite vrednost s praznim nizom. Naslednja formula na primer vrne današnji datum kot niz.
    =""& DANES()

Za zagotovitev, da se vrne določen podatkovni tip, lahko uporabite tudi naslednje funkcije:

Pretvarjanje realnih števil v cela števila

Scenarij: pogojne vrednosti in preizkušanje napak

Tako kot Excel ima tudi DAX funkcije, ki omogočajo preskus vrednosti v podatkih in vrnitev drugačne vrednosti na podlagi pogoja. Ustvarite lahko na primer izračunani stolpec, ki prodajalce označi kot »Prednostno « ali »Vrednost« , odvisno od zneska letne prodaje. Funkcije, ki preizkušajo vrednosti, so uporabne tudi za preverjanje obsega ali vrste vrednosti, da preprečite nepričakovane napake v podatkih, da bi prekinile izračune.

Ustvarjanje vrednosti na podlagi pogoja

Ugnezdene pogoje IF lahko uporabite za preskušanje vrednosti in pogojno ustvarjanje novih vrednosti. Naslednje teme vsebujejo nekaj preprostih primerov pogojne obdelave in pogojnih vrednosti:

Preverjanje napak v formuli

Za razliko od Excela ne morete imeti veljavnih vrednosti v eni vrstici izračunanega stolpca in neveljavnih vrednosti v drugi vrstici. To pomeni, da če pride do napake v katerem koli delu stolpca Power Pivot, je celoten stolpec označen z napako, tako da morate vedno popraviti napake formule, ki imajo za posledico neveljavne vrednosti.

Če na primer ustvarite formulo, ki deli z ničlo, boste morda dobili rezultat neskončnosti ali napako. Nekatere formule ne bodo uspešne tudi, če funkcija naleti na prazno vrednost, ko pričakuje številsko vrednost. Med razvojem podatkovnega modela je najbolje, da dovolite, da se napake prikažejo, tako da lahko kliknete sporočilo in odpravite težavo. Vendar pa morate pri objavljanju delovnih zvezkov vključiti ravnanje z napakami, da preprečite, da bi nepričakovane vrednosti povzročile neuspeh izračunov.

Če se želite izogniti vračanju napak v izračunanem stolpcu, uporabite kombinacijo logičnih in informacijskih funkcij za preverjanje napak in vedno vrnete veljavne vrednosti. V teh temah je nekaj preprostih primerov, kako to naredite v jeziku DAX:

Scenariji: Uporaba časovne inteligence

Funkcije časovnega obveščanja jezika DAX vključujejo funkcije, s katerimi lahko iz podatkov pridobite datume ali časovne obdobje. Te datume ali časovna obdobja lahko nato uporabite za izračun vrednosti v podobnih obdobjih. Funkcije časovne inteligence vključujejo tudi funkcije, ki delujejo s standardnimi časovnimi intervali, da lahko primerjate vrednosti v mesecih, letih ali četrtletjih. Ustvarite lahko tudi formulo, ki primerja vrednosti za prvi in zadnji datum določenega obdobja.

Če si želite ogledati seznam vseh funkcij časovne inteligence, glejte Funkcije časovne inteligence (DAX). Če želite namige za učinkovito uporabo datumov in ur v analizi dodatka Power Pivot, glejte Datumi v dodatku Power Pivot.

Izračun kumulativne prodaje

Naslednje teme vsebujejo primere izračuna zaključnega in začetnega stanja. Primeri omogočajo ustvarjanje tekočih bilanc v različnih intervalih, kot so dnevi, meseci, četrtletja ali leta.

Primerjava vrednosti skozi čas

V spodnjih temah so primeri primerjave vsot v različnih časovnih obdobjih. Privzeta časovna obdobja, ki jih podpira DAX, so meseci, četrtletja in leta.

Izračun vrednosti v časovnem obdobju po meri

Oglejte si spodnje teme za primere, kako pridobiti časovna obdobja po meri, na primer prvih 15 dni po začetku pospeševanja prodaje.

Če uporabljate funkcije časovnega obveščanja za pridobivanje nabora datumov po meri, lahko ta nabor datumov uporabite kot vhod v funkcijo, ki izvaja izračune, da ustvarite združevanje po meri v časovnih obdobjih. Oglejte si naslednjo temo za primer, kako to narediti:

  • Funkcija PARALLELPERIOD

    Opomba

    Če vam ni treba določiti časovnega obdobja po meri, vendar delate s standardnimi računovodskimi enotami, kot so meseci, četrtletja ali leta, priporočamo, da izračune izvedete s funkcijami časovnega obveščanja, ki so zasnovane za ta namen, kot so TOTALQTD, TOTALMTD, TOTALQTD itd.

Scenariji: razvrščanje in primerjava vrednosti

Če želite prikazati le zgornjih n elementov v stolpcu ali vrtilni tabeli, imate na voljo več možnosti:

  • S funkcijami v Excelu lahko ustvarite zgornji filter. Izberete lahko tudi število zgornjih ali spodnjih vrednosti v vrtilni tabeli. V prvem delu tega razdelka je opisano, kako filtrirate prvih 10 elementov v vrtilni tabeli. Če želite več informacij, glejte Excelovo dokumentacijo.
  • Ustvarite lahko formulo, ki dinamično razvršča vrednosti, in nato filtrirate po vrednostih razvrščanja ali uporabite vrednost razvrščanja kot razčlenjevalnik. V drugem delu tega razdelka je opisano, kako ustvarite to formulo in nato uporabite to razvrstitev v razčlenjevalniku.

Vsaka metoda ima prednosti in slabosti.

  • Filter Excel Top je preprost za uporabo, vendar je filter namenjen izključno prikazu. Če se podatki, na katerih temelji vrtilna tabela, spremenijo, morate ročno osvežiti vrtilno tabelo, da si ogledate spremembe. Če želite dinamično delati z razvrstitvami, lahko uporabite DAX, da ustvarite formulo, ki primerja vrednosti z drugimi vrednostmi v stolpcu.
  • Formula DAX je močnejša; poleg tega lahko z dodajanjem vrednosti razvrščanja v razčlenjevalnik preprosto kliknete razčlenjevalnik, da spremenite število prikazanih najvišjih vrednosti. Vendar pa so izračuni računsko dragi in ta metoda morda ni primerna za tabele z več vrsticami.

Prikaz le prvih desetih elementov v vrtilni tabeli

Prikaz zgornjih ali spodnjih vrednosti v vrtilni tabeli
  1. V vrtilni tabeli kliknite puščico dol v naslovu Oznake vrstic .
  2. Izberite Filtri> vrednostiTop 10.
  3. V pogovornem oknu Ime> stolpca Zgornjih 10 filtrov < izberite stolpec, ki ga želite razvrstiti, in število vrednosti, kot sledi:
    1. Izberite Zgoraj, če si želite ogledati celice z najvišjimi vrednostmi, ali Spodaj , če si želite ogledati celice z najnižjimi vrednostmi.
    2. Vnesite število zgornjih ali spodnjih vrednosti, ki si jih želite ogledati. Privzeta vrednost je 10.
    3. Izberite, kako želite prikazati vrednosti:
NameDescriptionItemsTo možnost izberite, če želite filtrirati vrtilno tabelo tako, da prikaže le seznam zgornjih ali spodnjih elementov glede na njihove vrednosti. OdstotekTo možnost izberite, če želite vrtilno tabelo filtrirati tako, da prikaže le elemente, ki seštevajo določen odstotek. VsotaTo možnost izberete, če želite prikazati vsoto vrednosti za zgornje ali spodnje elemente.
  1. Izberite stolpec z vrednostmi, ki jih želite razvrstiti.
  2. Kliknite V redu.

Dinamično razvrščanje elementov s formulo

V tej temi je primer uporabe jezika DAX za ustvarjanje razvrstitve, ki je shranjena v izračunanem stolpcu. Ker se formule DAX izračunajo dinamično, ste lahko vedno prepričani, da je razvrstitev pravilna, tudi če so se temeljni podatki spremenili. Ker je formula uporabljena v izračunanem stolpcu, lahko uporabite razvrstitev v razčlenjevalniku in nato izberete najboljših 5, prvih 10 ali celo 100 najboljših vrednosti.