Forum Discussion
Post ProdigyAverage % and Minimum %
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
Post ProdigyYes 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
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:
- po
Your solution works - just one question is it possible for the mimimum filter to exclude rows which have a certain value
e.g if % measure is 0% exlcude these rows when calculating the minimum for that store for the week - finding we have some shops where no data supplied on a certian day and these skewing the minimum calculation - looking to refine to calculate the minimum for the shop that week based on where they have supplied a figure for the measure for the day
Have added another filter to start of it and
put column<>0 and looks to work o.k. - po
Thanks for suggestion.
Another possibility when wish to jsut show data at store/week level could look to use is calculated measure of %age on the table and then include this twice on the table and select as average and as minimum in each case.