Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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:
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
Solved! Go to 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.
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.
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))
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.
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.
User | Count |
---|---|
123 | |
70 | |
67 | |
58 | |
53 |
User | Count |
---|---|
189 | |
94 | |
67 | |
63 | |
56 |