Når du kan bruke beregnede kolonner og beregnede felt

Viktig: Denne artikkelen er maskinoversatt, se ansvarsfraskrivelsen. Du finner den engelske versjonen av artikkelen her som referanse.

Når du først lærer du hvordan du bruker Power Pivot, oppdage de fleste brukere er den virkelige styrken i aggregere eller beregner et resultat på noen måte. Hvis dataene har en kolonne med numeriske verdier, kan du enkelt aggregere det ved å velge den i en pivottabell eller en Feltliste i Power View. Fordi det er numeriske, det vil automatisk bli summert, gjennomsnitt, telles, reiseplaner, eller velg ønsket type aggregering. Dette kalles en implisitt mål. Implisitt mål egner seg godt til rask og enkel aggregering, men de har begrensninger, og kan nesten alltid løse disse begrensningene med eksplisitt mål og beregnede kolonner.

La oss først se et eksempel der vi bruker en beregnet kolonne for å legge til en ny tekstverdi for hver rad i en tabell kalt produktet. Hver rad i Product-tabellen inneholder alle typer informasjon om hvert av produktene vi selger. Vi har kolonner for varenavn, fargen, størrelsen, Forhandlerprisen og så videre … Vi har en annen relatert tabell kalt produktkategori som inneholder en kolonne ProductCategoryName. Vi ønsker er for hvert produkt i Product-tabellen til å inkludere kategori produktnavn fra produktkategoritabellen. I våre Product-tabellen kan vi opprette en beregnet kolonne kalt produktkategori slik ut:

Beregnet kolonne for produktkategori

Vår nye produktkategori-formelen bruker BESLEKTEDE DAX-funksjonen til å få verdier fra kolonnen ProductCategoryName i den relaterte produktkategori-tabellen, og deretter angir du disse verdiene for hvert produkt (hver rad) i Product-tabellen.

Dette er et godt eksempel på hvordan vi kan bruke en beregnet kolonne til å legge til en fast verdi for hver rad som vi kan bruke senere i rader, kolonner, eller FILTEROMRÅDET i pivottabellen eller i en Power View-rapport.

La oss opprette et annet eksempel der vi vil beregne en fortjenestemargin for våre produktkategorier. Dette er et vanlig scenario selv i mange veiledninger. Vi har en salgstabellen i vår datamodell som har transaksjonsdata, og det er en relasjon mellom Sales-tabellen og produktkategori-tabellen. I Sales-tabellen har vi en kolonne som inneholder salgsbeløp og en annen kolonne som har kostnader.

Vi kan opprette en beregnet kolonne som beregner en profittbeløp for hver rad ved ved å trekke fra verdiene i kolonnen VAREFORBRUK fra verdier i kolonnen SalesAmount slik ut:

Fortjenestekolonne i Power Pivot-tabell

Nå kan vi opprette en pivottabell, og dra produktkategori-feltet til kolonner og vår nye Profit-feltet til verdier-området (en kolonne i en tabell i PowerPivot er et felt i feltlisten for pivottabell). Resultatet er et implisitt mål kalt Sum of Profit. Det er et samlet beløp med verdier fra profit-kolonnen for hver av flere produktkategorier. Vår resultatet ser slik ut:

Enkel pivottabell

I dette tilfellet fornuftig fortjeneste bare som et felt i verdier. Hvis vi skulle legge Profit i kolonner-området, ser vår pivottabellen slik ut:

Pivottabell uten brukbare verdier

Vår Profit-feltet gir ikke nyttig informasjon når det er plassert i kolonner, rader eller FILTRE. Det er bare fornuftig som en aggregert verdi i verdier-området.

Vi har gjort, er å opprette en kolonne kalt fortjenesten som beregner en fortjenestemargin for hver rad i Sales-tabellen. Vi deretter legges Profit til verdier-området i vår pivottabellen automatisk oppretting av et implisitt mål, der et resultat er beregnet for hver av produktkategoriene. Hvis du tror at vi egentlig beregnet fortjeneste for våre produktkategorier to ganger, er det riktig. Vi først beregnet fortjeneste for hver rad i Sales-tabellen, og vi deretter lagt Profit til verdier-området der det ble aggregert for hver av produktkategoriene. Hvis du tenker også vi egentlig ikke trenger for å opprette beregnede Profit-kolonnen, er du riktig. Men hvordan deretter vi beregne fortjenesten uten å opprette en beregnet kolonne fortjeneste?

Resultatregnskap, ville virkelig bedre beregnet som et eksplisitt mål.

Nå skal vi la vår beregnede Profit-kolonnen i Sales-tabellen og produktkategori i kolonner og fortjeneste i verdier for våre pivottabell for å sammenligne våre resultater.

I beregningsområdet av våre salgstabellen vi skal opprette et mål som heter Totalfortjenesten(for å unngå navnekonflikter). I slutten, vil det gi samme resultat som det vi gjorde før, men uten en beregnet kolonne fortjeneste.

Først i Sales-tabellen vi velge kolonnen salgsbeløp og deretter Autosummer for å opprette en eksplisitt Summen av salgsbeløpmål. Husk at et eksplisitt mål er ett vi opprette i beregningsområdet i en tabell i PowerPivot. Vi gjør det samme for VAREFORBRUK-kolonnen. Vi vil gi nytt navn til disse Totalt Salgsbeløp og Totalt VAREFORBRUK for å gjøre dem enklere å identifisere.

Autosummer-knappen i Power Pivot

Deretter oppretter vi en annen mål med denne formelen:

Totale fortjenesten: = [ totalt salgsbeløp]-[totalt VAREFORBRUK]

Merknad: Vi kan også skrive våre formelen som totalt Profit:=SUM([SalesAmount]) - SUM([COGS]), men ved å opprette separate totalt salgsbeløp og totalt COGS mål, vi kan bruke dem i vår pivottabell for, og vi kan bruke dem som argumenter i alle typer andre mål-formler.

Når du har endret vår nye totalfortjenesten mål formatet til valuta, kan vi legge den til vår pivottabell.

Pivottabell

Du kan se vår nye totalfortjenesten mål returnerer samme resultat som å opprette en beregnet kolonne Profit og plassere det i verdier. Forskjellen er vår totalfortjenesten mål er helt mer effektivt, og gjør vår datamodellen mer oversiktlig og enklere fordi vi beregning på tidspunktet og bare for feltene vi velger for våre pivottabell. Trenger vi ikke den beregnede kolonnen Profit likevel.

Hvorfor er denne siste delen viktig? Beregnede kolonner legge til data i datamodellen, og data tar minne. Hvis vi oppdaterer datamodellen, er også behandling ressursene som kreves omberegne alle verdiene i Profit-kolonnen. Vi ikke virkelig trenger å ta opp ressurser slik ut, fordi vi vil beregne fortjenesten når vi merker feltene vi vil fortjeneste for i pivottabellen, slik produktkategorier, område, eller etter dato.

La oss se på et annet eksempel. Ett der en beregnet kolonne oppretter resultatene ser ved første øyekast riktig, men...

I dette eksemplet vil vi beregne laveste salgsbeløpene som en prosentdel av totalsalget. Vi opprette en beregnet kolonne kalt % av salget i vår salg-tabellen, slik som dette:

Kolonnen % av beregnet salg

Vår formel tilstander: deler beløpet i salgsbeløp-kolonnen med summen av alle beløp i salgsbeløp-kolonnen For hver rad i Sales-tabellen.

Hvis vi opprette en pivottabell og legge til produktkategori i KOLONNENE og velger den nye % av salg -kolonnen for å plassere det i verdier, få vi en sum Sum av % av salget for hver av våre produktkategorier.

Pivottabell som viser sum av % av salg for produktkategorier

OK. Dette er fornøyd med så langt. Men la oss legge til en slicer. Vi legge til kalenderår og deretter velger du et år. I dette tilfellet velger vi 2007. Dette er hva vi få.

Feil resultat i en pivottabell for summen av % av salg

Ved første øyekast kan dette fremdeles vises riktig. Men vår prosentdeler bør virkelig totalt 100%, fordi vi vil du vite prosentvise totalt salg for hver av våre produktkategorier for 2007. Så hva som gikk galt?

Vår % av salg-kolonnen beregnet prosent for hver rad er verdien i kolonnen SalesAmount dividert med summen av alle verdiene i kolonnen SalesAmount. Verdier i en beregnet kolonne er løst. De er et uforanderlig resultat for hver rad i tabellen. Når vi har lagt til % av salget våre pivottabellen det ble aggregert som en sum for alle verdier i kolonnen SalesAmount. Summen av alle verdier i % av salg-kolonnen vil alltid være 100%.

Tips!: Pass på å lese kontekst i DAX-formler. Den inneholder en god forståelse av nivå radkontekst og filterkontekst, som er hva vi er som beskriver her.

Vi kan slette våre % av salg beregnet kolonne fordi den ikke kommer til å hjelpe oss. I stedet kan skal vi opprette et mål som beregner våre prosent av totalt salg, uavhengig av eventuelle filtre eller slicere som er brukt på riktig måte.

Husk TotalSalesAmount mål vi opprettet tidligere, som summerer ganske enkelt kolonnen SalesAmount? Vi bruker den som et argument i vårt totalfortjenesten mål, og vi skal bruke den på nytt som et argument i vår nye beregnede feltet.

Tips!: Opprette eksplisitt mål som totalt salgsbeløp og totalt VAREFORBRUK ikke er bare nyttig seg i en pivottabell eller en rapport, men de er også nyttige som argumenter i andre forholdsregler når du trenger resultatet som et argument. Dette gjør formler mer effektiv og enklere å lese. Dette er god praksis for datamodellering.

Vi oppretter et nytt mål med følgende formel:

% av totalt salg: =([Total SalesAmount]) / BEREGNE ([Totalt antall SalesAmount], ALLSELECTED())

Denne formelen står: dele resultatet fra totalt salgsbeløp summen av salgsbeløp uten kolonne eller rad filtre enn dem som er definert i pivottabellen.

Tips!: Pass på å lese om funksjonene CALCULATE og ALLSELECTED i DAX-referanse.

Nå hvis vi legger til vår nye % av totalsalget i pivottabellen, få vi:

Riktig resultat for summen av % av salget i pivottabellen

Som ser en bedre. Nå beregnes våre % av Sum salg for hver produktkategori som en prosentdel av totalt salg for året 2007. Hvis vi velger forskjellige år eller mer enn ett år i sliceren CalendarYear, vi få nye prosenter for våre produktkategorier, men vår totalsum fremdeles er 100%. Vi kan legge til andre slicere og filtre for. Vår % av Sum salg mål vil alltid gi en prosentdel av totalsalget uavhengig av eventuelle slicere eller filtre. Med mål beregnes alltid resultatet etter konteksten bestemmes ved feltene i kolonner og rader, og eventuelle filtre eller slicere som er brukt. Dette er utbytte av mål.

Her er noen retningslinjer som hjelper deg når du skal bestemme hvorvidt en beregnet kolonne eller et mål som passer best for en bestemt beregning trenger:

Bruke beregnede kolonner

  • Hvis du vil at nye dataene skal vises på rader, kolonner, eller i FILTRE i en pivottabell eller på en AKSE, forklaring eller DELARK etter i en Power View-visualisering, må du bruke en beregnet kolonne. Beregnede kolonner som kan brukes som et felt i et område, akkurat som vanlig kolonner med data, og hvis de numeriske de kan være aggregert i verdier for.

  • Hvis du vil at nye dataene skal være en fast verdi for rad. Hvis du for eksempel har du en datotabell med en kolonne med datoer, og du vil bruke en annen kolonne som inneholder bare tall i måneden. Du kan opprette en beregnet kolonne som beregner bare månedsnummer fra datoene i Date-kolonnen. Hvis du for eksempel = MONTH('Date'[Date]).

  • Hvis du vil legge til en tekstverdi for hver rad i en tabell, bruker du en beregnet kolonne. Felt med tekstverdier kan aldri aggregert i verdier. Hvis du for eksempel gir =FORMAT('Date'[Date],"mmmm") oss månedsnavnet for hver dato i dato-kolonnen i datotabellen.

Bruke mål

  • Hvis resultatet av beregningen alltid er avhengige av de andre feltene velger du i en pivottabell.

  • Hvis du må gjøre mer komplekse beregninger, som beregner et antall som er basert på et filter slags, eller beregne år-over år eller varians, bruker du et beregnet felt.

  • Hvis du vil holde størrelsen på arbeidsboken på et minimum og maksimere ytelsen, kan du opprette så mange av beregninger som mål som mulig. I mange tilfeller kan alle beregninger være mål, betydelig reduksjon av størrelsen på arbeidsboken og raskere Oppdater tid.

Behold i tankene, det er ikke noe problem med oppretting av beregnede kolonner som vi gjorde med våre Profit-kolonnen og aggregere det i en pivottabell eller en rapport. Det er egentlig en veldig god og enkel måte å lære om og lage egne beregninger. Når din forståelse av disse to svært kraftige funksjonene i Power Pivot blir større, bør du opprette mest effektivt og nøyaktig datamodellen kan du. For hjelper det du har lært her. Det finnes noen virkelig flotte ressurser det som kan hjelpe deg også. Her er noen få: kontekst i DAX-formler, aggregeringer i Power Pivotog DAX-Ressurssenter. Og mens det er litt mer avansert, og rettet mot regnskap og Økonomi eksperter, resultat Data Modeling and Analysis with Microsoft PowerPivot i Excel utvalgsgjennomsnittet lastes med flott datamodellering og formeleksempler.

Merknad: Ansvarsfraskrivelse for maskinoversettelse: Denne artikkelen er oversatt av et datasystem i stedet for en oversetter. Microsoft tilbyr disse maskinoversettelsene slik at brukere som ikke snakker engelsk, får tilgang til innhold om Microsoft-produkter, -tjenester og –teknologier. Ettersom artikkelen er maskinoversatt, kan den inneholde feil i vokabular, syntaks eller grammatikk.

Utvid ferdighetene dine
Utforsk opplæring
Vær først ute med de nye funksjonene
Bli med i Office Insiders

Var denne informasjonen nyttig?

Takk for tilbakemeldingen!

Takk for tilbakemeldingen! Det høres ut som det kan være lurt å sette deg i kontakt med én av våre Office-kundestøtteagenter.

×