Forum Discussion
Average % and Minimum %
Hi,
Have data below for each day of week for a shop and have 7 days of data for each shop each week.
Can calculate normal % age o.k but wondering how best to calculate what the average and minimum percentage is for each shop each week.
when try do min (<%age measure>) or average (%measure) get error
e.g below minimum would be 85.40, average would be adding the 7 percentages and diving by 7.
How can we best achieve this?
Can possibly look to use summarize to create table grouped by week and shop name but wonder if better /other approaches?
Thanks
Ok, I think I have a solution. I hope I am not misunderstanding or making bad assumptions about your data structure. I have mocked up some sample data for 2 stores, A and B. Store A has two weeks of data and store B has one week of data. In the data source, there is one record per day, per store as we clarified:
Next, due of the grain of the data, we can add a calculated column for our % calc into the Power BI dataset. Typically, having % calculation columns can lead to issues with aggregation, but for our purposes we can leverage this:
I also added a "Week" calculated column based on the WEEKNUM function that we will need to use later on as well.
To get our Min % calculation, we will rely on this % calculated column we just created. Here is what I used for the syntax, which resulted in what I expected for that value:
Min % per Store per Week = MINX(FILTER(FILTER(ALL(Data),Data[Store] = SELECTEDVALUE(Data[Store])),Data[Week]=SELECTEDVALUE(Data[Week])),Data[% Column])The Average % has a couple of additional steps. Basically, we need to create a Numerator and Denominator value per store per week. Here is what I used for those:Num per Store per Week = SUMX(FILTER(FILTER(ALL(Data),Data[Store] = SELECTEDVALUE(Data[Store])),Data[Week]=SELECTEDVALUE(Data[Week])),Data[Num])Denom per Store per Week = SUMX(FILTER(FILTER(ALL(Data),Data[Store] = SELECTEDVALUE(Data[Store])),Data[Week]=SELECTEDVALUE(Data[Week])),Data[Denom])Using those two fields, we can create our Avg % per store per week measure:Avg % per Store per Week = [Num per Store per Week] / [Denom per Store per Week]And here is the final output:Let me know if this is helpful/works for you.Ben
5 Replies
- Hi
Yes we have 1 record per shop per day and 2 measures delivered and scammed delivered from which we calculate the percentage.
We wish to then show the average of these and the lowest one of these
ThanksOk, I think I have a solution. I hope I am not misunderstanding or making bad assumptions about your data structure. I have mocked up some sample data for 2 stores, A and B. Store A has two weeks of data and store B has one week of data. In the data source, there is one record per day, per store as we clarified:
Next, due of the grain of the data, we can add a calculated column for our % calc into the Power BI dataset. Typically, having % calculation columns can lead to issues with aggregation, but for our purposes we can leverage this:
I also added a "Week" calculated column based on the WEEKNUM function that we will need to use later on as well.
To get our Min % calculation, we will rely on this % calculated column we just created. Here is what I used for the syntax, which resulted in what I expected for that value:
Min % per Store per Week = MINX(FILTER(FILTER(ALL(Data),Data[Store] = SELECTEDVALUE(Data[Store])),Data[Week]=SELECTEDVALUE(Data[Week])),Data[% Column])The Average % has a couple of additional steps. Basically, we need to create a Numerator and Denominator value per store per week. Here is what I used for those:Num per Store per Week = SUMX(FILTER(FILTER(ALL(Data),Data[Store] = SELECTEDVALUE(Data[Store])),Data[Week]=SELECTEDVALUE(Data[Week])),Data[Num])Denom per Store per Week = SUMX(FILTER(FILTER(ALL(Data),Data[Store] = SELECTEDVALUE(Data[Store])),Data[Week]=SELECTEDVALUE(Data[Week])),Data[Denom])Using those two fields, we can create our Avg % per store per week measure:Avg % per Store per Week = [Num per Store per Week] / [Denom per Store per Week]And here is the final output:Let me know if this is helpful/works for you.Ben
