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 primerih.

  • Izvajanje zapletenih izračunov
  • Delo z besedilom in datumi
  • Pogojne vrednosti in preverjanje napak
  • Uporaba podatkov o času
  • Razvrščanje in primerjava vrednosti

V tem članku

Začetek

Obiščite wiki o središču za vire DAX , kjer so na voljo raznovrstne informacije o jeziku DAX, vključno s spletnimi dnevniki, vzorci, informativnimi dokumenti in videoposnetki, ki so jih pripravili vodilni strokovnjaki v panožni panogi in Microsoft.

Scenariji: Izvajanje zapletenih izračunov

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

Ustvarjanje izračunov po meri za vrtilno tabelo

CALCULATE in CALCULATETABLE sta zmogljivi, prilagodljivi funkciji, uporabni za določanje izračunanih polj. S temi funkcijami lahko spremenite kontekst, v katerem se izvaja izračun. Prilagodite lahko tudi vrsto združevanja ali matematične operacije, ki naj se izvede. Za primere glejte te teme.

Uporaba filtra za formulo

Na večini mest, kjer funkcija DAX uporabi tabelo kot argument, lahko običajno posredujete filtrirano tabelo, in sicer tako, da namesto imena tabele uporabite funkcijo FILTER ali pa navedete izraz filtra kot enega od argumentov funkcije. V teh temah so prikazani primeri, kako ustvarite filtre in kako filtri vplivajo na rezultate formul. Če želite več informacij, glejte »Filtriranje podatkov v formulah jezika DAX«.

S funkcijo FILTER lahko določite pogoje filtra z uporabo izraza, medtem ko so druge funkcije oblikovane posebej za filtriranje praznih vrednosti.

Selektivno odstranjevanje filtrov za ustvarjanje dinamičnega razmerja

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

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

Na formule, ki jih uporabite v vrtilni tabeli, lahko vpliva kontekst vrtilne tabele, vendar lahko kontekst selektivno spremenite tako, da dodate ali odstranite filtre. V primeru v temi »ALL« je opisano, kako to naredite. Če želite ugotoviti 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 spreminjanje rezultatov glede na zasnovo vrtilne tabele.

Druge primere izračunavanja razmerij in odstotkov najdete v teh temah:

Uporaba vrednosti iz zunanje zanke

Poleg tega, da v izračunih uporablja vrednosti iz trenutnega konteksta, lahko DAX uporabi tudi vrednost iz prejšnje zanke pri ustvarjanju nabora povezanih izračunov. V tej temi je na voljo navodila za ustvarjanje formule, ki se sklicuje na vrednost iz zunanje zanke. Funkcija EARLIER podpira največ dve ravni ugnezdenih zank.

Če želite izvedeti več o kontekstu vrstice in povezanih tabelah ter kako uporabiti ta koncept v formulah, glejte Kontekst v formulah jezika DAX.

Scenariji: Delo z besedilom in datumi

V tem razdelku so navedene povezave do tem z referencami jezika DAX, ki vsebujejo primere pogostih scenarijev, ki vključujejo delo z besedilom, ekstrahiranje in sestavljanje datumskih in časovnih vrednosti ali ustvarjanje vrednosti na osnovi pogoja.

Ustvarjanje stolpca s ključem glede na združevanje

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

Compose a date based on date parts from a text date

Power Pivot za delo z datumi uporablja podatkovni tip SQL Server »Datum/ura«; če torej vaši zunanji podatki vsebujejo datume, ki so oblikovani drugače – če so na primer vaši datumi zapisani v območni obliki datuma, ki jih podatkovni mehanizem Power Pivot ne prepozna, ali če vaši podatki uporabljajo nadomestne ključe celega števila – boste morda morali uporabiti formulo DAX, da izvlečete dele datuma in nato sestavite dele v veljaven datum. Predstavitev časa.

Č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 za datum/čas tako, da uporabite to formulo:

=DATE(RIGHT([Vrednost1],4),LEFT([Vrednost1],2),MID([Vrednost1],2))

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

V teh 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 in tako zagotovite pravilno obravnavanje vrednosti. Te oblike zapisa se uporabljajo pri pretvarjanju vrednosti v nize ali iz nizov. V teh temah je tudi podroben seznam vnaprej določenih oblik, ki so na voljo za delo z datumi in števili.

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 spodnji temi je primer, kako z jezikom DAX ustvarite razvrstitev, 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 osnovni podatki spremenili. Ker je formula uporabljena v izračunanem stolpcu, lahko uporabite razvrstitev v razčlenjevalniku in nato izberete prvih 5, prvih 10 ali celo prvih 100 vrednosti.