Used DAX Measures in Power BI Sales Dashboard Of a Beverages Company
MEASURE_NAME | MEASURE_DESC | EXPRESSION |
---|---|---|
Category Sales | Sales Of Category | CALCULATE ( [Sales], ALLEXCEPT ( 'ProductMaster', ProductMaster[Category] ) ) |
Category Sales MTD | Category Sales Month Till Date | IF ( [ShowValueForDates], CALCULATE ( [Category Sales], DATESMTD ( Calendar[Date] ) ) ) |
Category Sales PMTD | Category Sales Previous Month Till Date | IF ( [ShowValueForDates], CALCULATE ( [Category Sales], CALCULATETABLE ( DATEADD ( Calendar[Date], -1, MONTH ), Calendar[DateWithSales] = TRUE ) ) ) |
Category Sales PYC | Category Sales Previous Year Complete | IF ( [ShowValueForDates] && HASONEVALUE ( 'Calendar'[Year] ), CALCULATE ( [Category Sales], PREVIOUSYEAR ( 'Calendar'[Date] ) ) ) |
Category Sales YTD | Category Sales Year Till Date | IF ( [ShowValueForDates], CALCULATE ( [Category Sales], DATESYTD ( Calendar[Date] ) ) ) |
Contribution Of Total | Contribution Of Total | IF([Sales]>0,[Sales]/[Total Sales]) |
Date of Last Purchase | Date of Last Purchase | LASTDATE('Sales Files'[Date]) |
Days Since Last Purchase | Days Since Last Purchase | VALUE ( CALCULATE ( MAX ( 'Sales Files'[Date] ), ALL ( 'Sales Files' ) ) - [Date of Last Purchase] ) |
Delta MS % MTD vs PMTD | Delta Market Share Month Till Date vs Previous Month Till Date | [My Company MTD MS %]-[My Company PMTD MS %] |
Delta MS % MTD vs PMTD Arrow | Delta Market Share Month Till Date vs Previous Month Till Date Arrow | SWITCH ( TRUE (), [Delta MS % MTD vs PMTD] <= 0, UNICHAR ( 9660 ), [Delta MS % MTD vs PMTD] >= 0.0001, UNICHAR ( 9650 ), BLANK () ) |
Delta MS % YTD vs PYC | Delta Market Share Year Till Date vs Previous Year Complete | [My Company YTD MS %]-[My Company PYC MS %] |
Delta MS % YTD vs PYC Arrow | Delta Market Share Year Till Date vs Previous Year Complete Arrow | SWITCH ( TRUE (), [Delta MS % YTD vs PYC] <= 0, UNICHAR ( 9660 ), [Delta MS % YTD vs PYC] >= 0.0001, UNICHAR ( 9650 ), BLANK () ) |
Growth % MTD vs PMTD | Growth % Month Till Date vs Previous Month Till Date | VAR ValueCurrentPeriod = [Sales MTD] VAR ValuePreviousPeriod = [Sales PMTD] VAR Delta = IF ( NOT ISBLANK ( ValueCurrentPeriod ) && NOT ISBLANK ( ValuePreviousPeriod ), ValueCurrentPeriod - ValuePreviousPeriod ) RETURN DIVIDE(Delta,[Sales PYTD]) |
Growth % YTD vs PYTD | Growth % Year Till Date vs Previous Year Till Date | VAR ValueCurrentPeriod = [Sales] VAR ValuePreviousPeriod = [Sales PYTD] VAR Delta = IF ( NOT ISBLANK ( ValueCurrentPeriod ) && NOT ISBLANK ( ValuePreviousPeriod ), ValueCurrentPeriod - ValuePreviousPeriod ) RETURN DIVIDE(Delta,[Sales PYTD]) |
Lost Customers | Lost Customers | CALCULATE ( DISTINCTCOUNT ( 'Sales Files'[Store Code] ), FILTER ( 'Sales Files', [Days Since Last Purchase] > 30 ) ) |
MS % | Market Share | IFERROR([Sales]/[Category Sales],BLANK()) |
MS % PMTD | Market Share Previous Month Till Date | IFERROR([Sales PMTD]/[Category Sales PMTD],BLANK()) |
MS % PYC | Market Share Previous Year Complete | DIVIDE([Sales PYC],[Category Sales PYC]) |
MTD Growth Arrow | Month Till Date Growth Arrow | SWITCH ( TRUE (), [Growth % MTD vs PMTD] <= 0, UNICHAR ( 9660 ), [Growth % MTD vs PMTD] >= 0.0001, UNICHAR ( 9650 ), BLANK () ) |
MTD MS % | Month Till Date Market Share | IFERROR([Sales MTD]/[Category Sales MTD],BLANK()) |
My Company MS % | My Company Market Share | CALCULATE([MS %],ProductMaster[Company]="My Company") |
My Company MTD MS % | My Company Month Till Date Market Share | CALCULATE([MTD MS %],ProductMaster[Company]="My Company") |
My Company PMTD MS % | My Company Previous Month Till Date Market Share | CALCULATE([MS % PMTD],ProductMaster[Company]="My Company") |
My Company PYC MS % | My Company Previous Year Complete Market Share | CALCULATE([MS % PYC],ProductMaster[Company]="My Company") |
My Company YTD MS % | My Company Year Till Date Market Share | CALCULATE([YTD MS %],ProductMaster[Company]="My Company") |
New Customers | New Customers | VAR currentDate = MIN ( 'Sales Files'[Date] ) VAR currentCustomers = CALCULATETABLE ( VALUES ( 'Sales Files'[Store Code] ), 'Sales Files'[Date] >= currentDate ) VAR pastCustomers = CALCULATETABLE ( VALUES ( 'Sales Files'[Store Code] ), ALL ( 'Calendar'[Date] ), 'Calendar'[Date] < currentDate ) VAR newCustomers = EXCEPT ( currentCustomers, pastCustomers ) RETURN COUNTROWS ( DISTINCT ( newCustomers ) ) |
Repeat Customers | Repeat Customers | IFERROR([Unique Customers Billed]-[New Customers],"") |
Sales | Sales | SUMX('Sales Files',[QTY_Case]+([Qty_Bottle]/[Case Size])) |
Sales MTD | Sales Month Till Date | IF([ShowValueForDates],CALCULATE([Sales],DATESMTD(Calendar[Date]))) |
Sales PMTD | Sales Previous Month Till Date | IF ( [ShowValueForDates], CALCULATE ( [Sales], CALCULATETABLE ( DATEADD ( Calendar[Date], -1, MONTH ), Calendar[DateWithSales] = TRUE ) ) ) |
Sales PYC | Sales Previous Year Complete | IF ( [ShowValueForDates] && HASONEVALUE ( 'Calendar'[Year] ), CALCULATE ( [Sales], PREVIOUSYEAR ( 'Calendar'[Date] ) ) ) |
Sales PYTD | Sales Previous Year Till Date | IF ( [ShowValueForDates], CALCULATE ( [Sales], CALCULATETABLE ( DATEADD ( 'Calendar'[Date], -1, YEAR ), 'Calendar'[DateWithSales] = TRUE ) ) ) |
Sales YTD | Sales Year Till Date | IF([ShowValueForDates],CALCULATE([Sales],DATESYTD(Calendar[Date]))) |
ShowValueForDates | Return TRUE where Last Date With Data is Avalable | VAR LastDateWithData = CALCULATE ( MAX ( 'Sales Files'[Date] ), ALL ( 'Sales Files') ) VAR FirstDateVisible = MIN ( 'Calendar'[Date] ) VAR Result = FirstDateVisible <= LastDateWithData RETURN Result |
Total Sales | Total Sales | CALCULATE([Sales],ALL(StoreMaster[STATE])) |
Unique Customers Billed | Unique Customers Billed | CALCULATE(DISTINCTCOUNT(StoreMaster[Store Code]),FILTER('Sales Files',[Sales]>0)) |
YTD Growth Arrow | Year Till Date Growth Arrow | SWITCH ( TRUE (), [Growth % YTD vs PYTD] <= 0, UNICHAR ( 9660 ), [Growth % YTD vs PYTD] >= 0.0001, UNICHAR ( 9650 ), BLANK () ) |
YTD MS % | Year Till Date Market Share | IFERROR([Sales YTD]/[Category Sales YTD],BLANK()) |
Download Dashboard File
Go to This Link - Download pbix File
No comments:
Post a Comment