donderdag 13 december 2012

Blog 8 TOTALYTD van een DISTINCTCOUNT


Als voorbeeld  wil ik periodiek en YTD de gemiddelde omzet per klant in een periode berekenen. Er zijn meerdere omzetregels van één klant in een periode, dus kan ik niet de periodieke omzet delen door het aantal omzetregels. Bepaal eerst het aantal unieke  klanten per periode.


Ik tel in een Salestabel periodiek het aantal unieke klantID's bij elkaar op met de volgende measure: DISTINCTCOUNT(Sales[KlantID]) en dat werkt prima.
Nu wil ik met de uitkomst van iedere periode een TOTALYTD berekening maken. 

Kortom, van periode 1 t/m x het periodieke aantal unieke KlantID's bij elkaar optellen.

Wat wil ik :Stel, ik heb in periode 1, 3 unieke klantID's en in periode 2 ook 3 unieke klantID's. Dan wil ik dat de YTD functie mij 3 x 2 = 6 ID's totaal geeft, maar omdat in periode 1&2 de ID's identiek zijn, is de uitkomst van de YTD functie hetzelfde, namelijk ook 3.

De distinctcount functie moet dus voor de YTD berekening periodiek worden uitgeschakeld.

Na enig speurwerk en tips van andere blogs kwam ik tot de volgende oplossing die ik hieronder in een voorbeeld heb uitgewerkt:

Hieronder een tabel Sales waarin per week diverse omzetten per KlantID zijn vastgelegd


Hieronder een tabel Weektable met de weeknummers.



Maak een calculated kolom met daarin de unieke sleutel tussen de tabellen Weektable en SalesIk noem de kolom Wk-Yr en maak de kolom date op als datumtabel. 

Vervolgens:maak een eenvoudige draaitabel en selecteer de weken en Sales.



Bereken nu middels een meting (measure) in de draaitabel het aantal unieke KlantID's per week m.b.v. onderstaande formule: 

DIS = DISTINCTCOUNT(Sales[KlantID])
Je ziet in de tabel voor week31 3 unieke ID, voor week37 8 en week38 7.

Als ik op deze uitkomst de YTD functie toepas, worden niet het aantal unieke ID's per periode bij elkaar opgeteld, maar berekent de YTD functie het aantal unieke ID's over meerdere periodes binnen één jaar. (Kijk maar eens naar het eindtotaal van Distinct Clients, dit is geen optelling van de weken.)

De uitkomst moet dus per periode gesommeerd worden alvorens te salderen over meerdere periodes.

Voor sommering gebruik ik:

DIS3= SUMX(DISTINCT(Sales[week]);[DIS])

De uitkomst is precies hetzelfde, alleen is het nu een sommering van unieke ID's per week geworden. De laatste stap is de TOTALYTD functie gebruiken om in enig jaar het aantal gesommeerde ID's per week te salderen.

YTD= TOTALYTD([DIS3];Weektable[date])

En ja hoor, het aantal unieke ID's per periode worden bij elkaar opgeteld!


Het kan ook korter:

1. DIS = DISTINCTCOUNT(Sales[KlantID])
2. YTD=TOTALYTD(SUMX(DISTINCT(Sales[week]);[Dis]);Weektable[date])