Untitled

mail@pastecode.io avatar
unknown
plain_text
21 days ago
2.2 kB
1
Indexable
Never
>> Salesperson table definition
==================================================
Salesperson =
'Salesperson (Performance)'


>> Date table definition
==================================================
Date =
CALENDARAUTO(6)


>> Date table: Year column definition
==================================================
Year =
"FY" & YEAR('Date'[Date]) + IF(MONTH('Date'[Date]) > 6, 1)


>> Date table: Quarter column definition
==================================================
Quarter =
'Date'[Year] & " Q"
    & IF(
        MONTH('Date'[Date]) <= 3,
        3,
        IF(
            MONTH('Date'[Date]) <= 6,
            4,
            IF(
                MONTH('Date'[Date]) <= 9,
                1,
                2
            )
        )
    )



>> Date table: Month column definition
==================================================
Month =
FORMAT('Date'[Date], "yyyy MMM")


>> Date table: MonthKey column definition
==================================================
MonthKey =
(YEAR('Date'[Date]) * 100) + MONTH('Date'[Date])


>> Sales table: Avg Price measure definition
==================================================
Avg Price =
AVERAGE(Sales[Unit Price])


>> Sales table: Additional measure definitions (use one definition at a time)
==================================================
Median Price =
MEDIAN(Sales[Unit Price])

Min Price =
MIN(Sales[Unit Price])

Max Price =
MAX(Sales[Unit Price])

Orders =
DISTINCTCOUNT(Sales[SalesOrderNumber])

Order Lines =
COUNTROWS(Sales)


>> Targets table: Target measure definition
==================================================
Target =
IF(
	HASONEVALUE('Salesperson (Performance)'[Salesperson]),
	SUM(Targets[TargetAmount])
)


>> Targets table: Variance measure definition
==================================================
Variance =
IF(
	HASONEVALUE('Salesperson (Performance)'[Salesperson]),
	SUM(Sales[Sales]) - [Target]
)


>> Targets table: Variance Margin measure definition
==================================================
Variance Margin =
DIVIDE([Variance], [Target])
Leave a Comment