Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I've been reviewing a ton of suggestions for identifiying outliers within my dataset, but nothing that is quite close to what I need to accomplish.
I've got a list of stores and their sales revenue for the previous 18 months. Not all stores had sales during each month or each year for that matter. Some stores saw exponetial growth while others have dramatically decreased sales.
My range for what I define as an outlier may change over time, but currently it's any store that is outside of the -200% or 200% sales growth YoY.
To define my Sales Growth % I'm using a couple of measures.
1. Total Sales -
2. Total Sales YTD -
// my fiscal year begins 9/1
3. Total Sales PYTD -
4. Sales Growth % -
Solved! Go to Solution.
Very difficult to give you a specific answer with the information provided. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
However, if you want to exclude some stores in certain calculations, you should be able to structure your calculations like this:
Measure =
VAR __Table =
FILTER(
SUMMARIZE(
'Sales Table',
[Store ID],
"__SalesGrowth%",[Sales Growth %]
),
[__SalesGrwoth%] > -2 && [__SalesGrowth%] < 2
)
RETURN
<some calculation over __Table>
Very difficult to give you a specific answer with the information provided. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
However, if you want to exclude some stores in certain calculations, you should be able to structure your calculations like this:
Measure =
VAR __Table =
FILTER(
SUMMARIZE(
'Sales Table',
[Store ID],
"__SalesGrowth%",[Sales Growth %]
),
[__SalesGrwoth%] > -2 && [__SalesGrowth%] < 2
)
RETURN
<some calculation over __Table>
This isn't exactly an elegant solution, but you could a calculated column in your store table (assuming they are separate tables)
% Change =
VAR
CYTD = --insert your formula for YTD here
VAR
PYTD = --insert your formula for PYTD here
RETURN
DIVIDE
CYTD,
PYTD,
BLANK()
)
That would make it very easy to calculate % change per store.
From there excluding stores becomes very simple...
FILTER(
Stores,
[%Change] <= 2 &&
[%Change] >= -2
)
Going to correct myself on something...your formulas should be like this:
CYTD = SUMX(
FILTER(
RELATEDTABLE(Sales),
--Set Filter for this year
Sales
)
PYTD = CALCULATE(
CYTD,
SAMEPERIODLASTYEAR(Dates[Date])
)
for this year and last year you can try like
YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"12/31"))
This Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD((ENDOFYEAR('Date'[Date])),"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
Last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
for outliers
https://powerbi.microsoft.com/en-us/blog/visual-awesomeness-unlocked-box-and-whisker-plots/
https://www.mssqltips.com/sqlservertip/5547/anomaly-detection-without-any-coding-using-power-bi/
https://appsource.microsoft.com/en-us/product/power-bi-visuals/WA104381449?src=office&tab=Overview
User | Count |
---|---|
94 | |
79 | |
74 | |
63 | |
59 |
User | Count |
---|---|
110 | |
101 | |
84 | |
64 | |
62 |