Different Views
1. Report View
2. Data/Table View
3. Relationship/Model View
Panes
1. Filter
2. Visualizations
3. Data (Fields)
Visualizations Pane
1. Visual Tab
2. Format Tab
3. Analysis Tab
---------------------------
My First Dashboard
Insert Column Chart, Donut Chart,...
Chart Formatting
Insert Card
Insert Slicer
Insert Text Box
02 Product Sales:
Transform Data (Power Query)
Home--> Split Column --> By Delimiter
Format --> Trim, Uppercase, Add Prefix
Home-->Close & Apply
Insert Map Chart
Card Setting:
Callout Value:
Font: Segoe UI Semibold Size: 25 Color: White
Category Label:
Font: Segoe UI Semibold Size: 13 Color: White
Properties:
Size:
Height:100 Width: 175
Effects:
Background: Any Dark Color
Add Calculated Column
Table View --> Table Tools Menu --> Add Column
Profit = 'Products sales'[Sales]-'Products sales'[Cost]
GST = 'Products sales'[Sales]*0.18
Grand Total = 'Products sales'[Sales] + 'Products sales'[GST]
Tax = 'Products sales'[Profit]*0.25
Add Measures
Report View --> New Measures
Total Sales = SUM('Products sales'[Sales])
Avg Sales = AVERAGE('Products sales'[Sales])
Max Sales = MAX('Products sales'[Sales])
Min Sales = MIN('Products sales'[Sales])
No.of Orders = COUNT('Products sales'[Order ID])
No.of Cities = DISTINCTCOUNT('Products sales'[City])
-------------------------------
03 Dax Sales
Total Cost = SUM(Data[Cost])
Total Sales = SUMX(Data,Data[Quantity]*Data[Price])
Avg Sales = AVERAGEX(Data,Data[Price]*Data[Quantity])
Max Sales = MAXX(Data,Data[Price]*Data[Quantity])
Min Sales = MINX(Data,Data[Price]*Data[Quantity])
Total Profit = [Total Sales] - [Total Cost]
Profit % = DIVIDE([Total Profit],[Total Cost])
04 Relationship
Total Sales = SUMX(OrderDB,OrderDB[Quantity]*RELATED(ProductDB[Price]))
Avg Sales = AVERAGEX(OrderDB,OrderDB[Quantity]*RELATED(ProductDB[Price]))
Max Sales = MAXX(OrderDB,OrderDB[Quantity]*RELATED(ProductDB[Price]))
Min Sales = MINX(OrderDB,OrderDB[Quantity]*RELATED(ProductDB[Price]))
To Create Measure Table:
1. Home-->Enter Data-->Give Table Name
2. Select the new table from Data Pane and create any measure
eg: Records = COUNTROWS(TableName)
>3. Hide Column1
Calendar Table
1. Copy the DAX code from "10 Calendar Table.txt"
2. Modeling Tab-->Create Table and paste the code in formula bar
3. Goto Table Tools (Menu)-->Mark as Date Table-->Select "Date" column from that list
4. Goto Relationship View and Create Relationship between Calendar Table and Fact(Main) Table
5. Sort by Column (Month ---> Month NO, Qtr ---> Qtr No)
CalendarTable
ADDCOLUMNS (
CALENDARAUTO(),
//CALENDAR(DATE(2022,01,01),DATE(2025,12,31)),
"Year", YEAR ( [Date] ),
"Month", FORMAT ( [Date], "mmm" ),
"Day", DAY( [Date] ),
"Quarter", "Qtr " & QUARTER([Date]),
"Qtr", "Q" & QUARTER([Date]),
"Quarter No", QUARTER([Date]),
"MonthNo", Month( [Date]),
"MonthFullName", FORMAT ( [Date], "mmmm" ),
"WeekDay", FORMAT ( [Date], "ddd" ),
"WeekDayNo", WEEKDAY ( [Date] ),
"YearMonth", DATE(YEAR([Date]),MONTH([Date]),01)
)
Day 3
03 Dax Sales:
Time intelligence functions:
LY Sales = CALCULATE([Total Sales],SAMEPERIODLASTYEAR(CalendarTable[Date]))
LY Sales2 = CALCULATE([Total Sales],DATEADD(CalendarTable[Date],-1,YEAR))
LY Sales Var = [Total Sales] - [LY Sales]
LY Sales Var % (YoY) = DIVIDE([LY Sales Var],[LY Sales])
PM Sales = CALCULATE([Total Sales],DATEADD(CalendarTable[Date],-1,MONTH))
PM Sales Var = [Total Sales] - [PM Sales]
PM Sales Var % (MoM) = DIVIDE([PM Sales Var],[PM Sales])
YTD Sales = TOTALYTD([Total Sales],CalendarTable[Date])
LY YTD Sales = CALCULATE([YTD Sales], SAMEPERIODLASTYEAR(CalendarTable[Date]))
QTD Sales = TOTALQTD([Total Sales],CalendarTable[Date])
---------------------------
05 SuperStore Dashboard
Different Char Type: Clustered, Stacked, 100% Stacked
Conditional Formatting (Chart): Gradient, Rules, Field Values
CF Sales = IF([Total Sales]>1200000,"#0dbab1","#39ace6")
CF Sales2 = IF([Total Sales]>1600000,"DarkSeaGreen",IF([Total Sales]>1200000,"SlateGrey",IF([Total Sales]>800000,"Gold","LightCoral")))
CF Profit = IF([Total Profit]>0,"Teal","Salmon")
Conditional Formatting (Table): Background Color, Font Color, Data bars, Icon
-------------------