- Forums
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Integrations with Files and Services
- Mobile Apps
- Developer
- Let's Talk Data
- Custom Visuals Development Discussion
- Community Support
- Welcome to the Community
- Community Feedback
- Community Help Blog
- Training and Consulting
- Dashboard in a Day
- EdX Specific Training Discussion Forum

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- Re: Average % and Minimum %

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

po

Member

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-30-2019
08:19 AM

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

Solved! Go to Solution.

1 ACCEPTED SOLUTION

Accepted Solutions

bhpage

Regular Visitor

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-30-2019
10:55 AM

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:

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:

Using those two fields, we can create our Avg % per store per week measure:

And here is the final output:

Let me know if this is helpful/works for you.

Ben

5 REPLIES 5

bhpage

Regular Visitor

Re: Average % and Minimum %

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-30-2019
08:52 AM

Is this the grain of your data? E.g. one record per day, per store? Or is your data more granular, and you intend to roll it up to this level in your visuals?

po

Member

Re: Average % and Minimum %

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-30-2019
09:19 AM

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

Thanks

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

Thanks

bhpage

Regular Visitor

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-30-2019
10:55 AM

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:

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:

Using those two fields, we can create our Avg % per store per week measure:

And here is the final output:

Let me know if this is helpful/works for you.

Ben

po

Member

Re: Average % and Minimum %

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-31-2019
02:57 AM

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.

Highlighted
##

po

Member

Re: Average % and Minimum %

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-31-2019
08:20 AM

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.