Average Days on Site

DAX DAX Calculations
1

Hi everyone,

I’m trying to create a measure which calculates (Average) days on site for many different locations. I’m able to get the days on each site based on the measure below.

Days on Site =
CALCULATE (
COUNTROWS ( DISTINCT ( ‘Lem Entry’[Date])
))

My PBI table looks looks like the attached.

data.csv (190 Bytes)
How would I create a measure that would show the average of those ‘days on site’ if I created one card?

Thanks!

2

@cms418,

Welcome to the forum – great to have you here!

Give this measure a go and see how it works. Without a PBIX file I’m guessing in some cases what your data model looks like and your fields are named. Generally, you will get the best response/solution on the forum if you provide your Power BI file and a mockup of the results you’re looking for. But see how this works, and if you have problems with it, give a shout and we can work through more specific solution with your actual data.

Avg Days on Site =

VAR vTable =
    ADDCOLUMNS (
        SUMMARIZE ( 'Lem Entry', 'Lem Entry'[Site] ),
        "@DaysOnSite", [Days on Site]
    )
RETURN
    AVERAGEX ( vTable, [@DaysOnSite] )
  • Brian
1 Like
3

This seems to be working Brian! Wow, thanks- I’d love to share the file, however a bit concerned to share the companies’ financials. How do we get around that?

Just a new member, so advice would be appreciated :slight_smile:

4

welcome to the forum @cms418 a couple of solutions for ‘getting around’ the problem of sharing a dataset on the forum:

  1. check out this video:
    https://forum.enterprisedna.co/t/how-to-mask-sensitive-data-in-power-bi/7788?u=heather

  2. mock up a simple version of your data that demonstrates the problem (preferably with all the tables involved in the issue)

  3. in many cases, a generic sample can help, and an external tool has been created for this purpose:
    New External Tool to Create Practice Datasets

3 Likes
5

@cms418,

Great – glad to hear that worked well for you. If you haven’t already, be sure to subscribe to the Enterprise DNA YouTube channel – lots of great content, and I’ve got a video coming out early next week that addresses this specific issue (getting measures in cards to calculate correctly) in detail.

In terms of the confidential/sensitive data problem, that’s really common issue. To help people on the forum provide their data without compromising sensitivity, I put together the following video on masking information. Good thing to keep in your back pocket for the next problem requiring forum support:

Always great to have new folks here. It’s a really wonderful community, and I think you’ll enjoy it and learn a lot. I learn new stuff every day here…

– Brian

1 Like