Group Measure and Count the number of members in each group.

[Anonymous]

Hi community,

I am still learning and i wanted your help. 

Sample of data: (Weekly scale)

Year	Quarter	WeekNumber	Model	Site	Metric	Actual	Value
2018	1	1	A	10	Sales	1000	1010
2018	1	2	A	10	Sales	1050	1020
2018	1	3	A	10	Sales	1030	1000
2018	1	4	A	10	Sales	1100	1040
2018	1	5	A	10	Sales	2000	1500
2018	1	6	A	10	Sales	2500	2000

The above data has the below number of categories for each column.

Years: 1

Quarters: 4

Weeks:52

Models: 2

Sites: 255

Metrics: 4

I caclulated the MAPE using a measure calculation as shown below. 

MAPE_mean_wk = AVERAGEX(Weekly_Actual,(ABS([WeeklyActual]-[WeeklyValue]))/ ABS([WeeklyActual]) ) *100

OBJECTIVE: I want to bucket the MAPE measure into groups of 0-5%, 5-10% 10-15%, etc. , and calculate the number of sites in each group while i can also filter by Models, Metrics, Quarters. 

Desired Outcome:

	Sites Count
0-5%	60
5-10%	80
10-15%	70
15-20%	40
>20%	5
Total:	255

If no filter is selected, i want the average across all data. For example, if i filter to only quarter 1 i want average MAPE for each strore over quarter 1.

What i have tried so far, i created the below column,

Bucket Range2 = SWITCH( TRUE(),
Weekly_Actual[MAPE_mean_wk]<=5,"0-5%",
Weekly_Actual[MAPE_mean_wk]<=10,"5-10%",
Weekly_Actual[MAPE_mean_wk]<=15,"10-15%",
Weekly_Actual[MAPE_mean_wk]<=20,"15-20%",
Weekly_Actual[MAPE_mean_wk]<=25,"20-25%",
Weekly_Actual[MAPE_mean_wk]<=30,"25-30%",
Weekly_Actual[MAPE_mean_wk]<=35,"30-35%",
Weekly_Actual[MAPE_mean_wk]<=40,"35-40%",
Weekly_Actual[MAPE_mean_wk]<=50,"40-50%",
Weekly_Actual[MAPE_mean_wk]>50,"50%+")

and then i added it to a table with a distinctCount of Sites but the outcome is not as expected.

I think the problem is that it does not aggregate across the other columns but i couldn't find a way so far. 

Any help will be highly appreciated. 

[v-shex-msft · Accepted solution]

Hi @Anonymous ,

Current power bi not allow to create dynamic calculated column/table based on filter/slicer. For this scenario, you can write measure to achieve dynamic changes. (notice: if you use this measure in calculated column, its calculated result will been fixed not dynamic changes)

In my opinion, I'd like to suggest you create a table with all type of ranges, then write a measure formula to calculate with original table based on current category label.

If you confused on coding formula, please share pbix file with detail sample data for test.

Regards,

Xiaoxin Sheng