Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
mabdelmajeed
Regular Visitor

Upper and lower control limit based on DISTINCTCOUNT of IDs

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:
mabdelmajeed_0-1733234749137.png

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):

mabdelmajeed_1-1733234961974.png

 

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



 

 

1 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))

 

vjialongymsft_0-1733714746114.png

 

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])



vjialongymsft_1-1733714835190.png

 

 

 

 

 

 

Best Regards,

Jayleny

 

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

 

View solution in original post

5 REPLIES 5
mabdelmajeed
Regular Visitor

Hi @v-jialongy-msft 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. 


Hi @mabdelmajeed 


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

 

 

 

Best Regards,

Jayleny

Hi @v-jialongy-msft, 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

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))

 

vjialongymsft_0-1733714746114.png

 

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])



vjialongymsft_1-1733714835190.png

 

 

 

 

 

 

Best Regards,

Jayleny

 

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

 

v-jialongy-msft
Community Support
Community Support

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.

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.