Upper and lower control limit based on DISTINCTCOUNT of IDs

[mabdelmajeed]

Hi all, 

I have a libne cahrt that can be drilled down to a month or week level. Moreover, I want to have an average line from the analytics pane. And I wnat to create an upper and lower control limits based on the DISTINCTCOUNT of IDs. The rule of the limits are:

Average + 2*STD >>> Upper

Average - 2*STD >>> Lower

I have built the following measure:

UCL = AVERAGEX(Sheet1, DISTINCTCOUNT(Sheet1[id]) +2*STDEVX.P(Sheet1, DISTINCTCOUNT(Sheet1[id])))

LCL = AVERAGEX(Sheet1, DISTINCTCOUNT(Sheet1[id]) - 2*STDEVX.P(Sheet1, DISTINCTCOUNT(Sheet1[id])))

But the outcome is definitely wrong:

The average is supposed to be almost at the middle and the two control limits are supposed to be at equal distances from the average line.. Like this for example (The example is from another post):

I hope I can get a help with such aclculation here. I have attached a workbook with data.

https://fromsmash.com/qthqz5bpw~-dt

Many thanks in advance!

BR, 

MA

[mabdelmajeed]

Hi @Anonymous Thanks for your reply. So do you suggest aggregating the data on a monthly -or weekly- level and calcualting the STD? May be I can try such approach. 

[Anonymous]

Hi @mabdelmajeed 

Your idea sounds great. Give it a shot, and if you run into any issues, just let me know.

Best Regards,

Jayleny

[mabdelmajeed]

Hi @Anonymous, I'm getting back to you gain, as I tried to do it on a monthy level (same concept like daily) but it still resulting in a wrong number of average for example and it fluctuates across months as well. I want the average line to be fixed and equivalent to that arithmitic average line generated from the PBI Analytics pane. 

Here is my measure that I used:
FixedAverageMonthlyPercentageLine =
AVERAGEX(
SUMMARIZE(
Sheet1,
Sheet1[month], // Group by "month"
"PercentagePerMonth",
DISTINCTCOUNT(Sheet1[id]) * 1.0 / CALCULATE(DISTINCTCOUNT(Sheet1[id]), ALL(Sheet1))
),
[PercentagePerMonth]
)

it did not work and the other trials auch did not work. I would appreciate your help with calulating that average. 

Many thanks in advance!

BR, 
MA

[Anonymous · Accepted solution]

Hi @mabdelmajeed 

First, you can create a distinct count by month.

distinct count by month = 
VAR _select_category = SELECTEDVALUE('Sheet1'[category])
VAR _select_year = YEAR(SELECTEDVALUE('Sheet1'[date]))
VAR _select_month = MONTH(SELECTEDVALUE('Sheet1'[date]))

RETURN
CALCULATE(DISTINCTCOUNT('Sheet1'[id]),FILTER(ALL('Sheet1'),'Sheet1'[category] = _select_category && YEAR('Sheet1'[date]) = _select_year && MONTH('Sheet1'[date]) = _select_month))

Then you can calculate the average based on the month.

average = 
VAR _select_category = SELECTEDVALUE('Sheet1'[category])
RETURN
AVERAGEX(FILTER(ALL(Sheet1),'Sheet1'[category] = _select_category),[distinct count by month])

Best Regards,

Jayleny

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @mabdelmajeed 

The result of DISTINCTCOUNT(Sheet1[id]) is the same in each row, so the standard deviation is always calculated as 0, resulting in the same result for both of your measures.

Best Regards,

Jayleny

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.