Forum Discussion

funderburkj91's avatar
funderburkj91
Frequent Visitor
6 years ago

Problems Grouping by Week from Daily Data Source on Same Tab

Hi Community,

 

I have a data source that shows daily data broken out by client that I am having problems grouping as a whole week in certain visualizations. I have a line chart, matrix, and other visualizations that allow me to drill down from week to day, as I want it to, but I have several cards and a bar chart that I would only like to show the weekly total.

 

Starting with the card visualizations, I have a measure that takes the percent of hours serviced divided by the expected service hours (called % of Expected Hours). I would like a card to show me the count of how many times a specific program and week combination fell with 95% and 105%. For example, if the % of expected hours is 96% on the week ending 8/16 for Program A, it would count on the card, but if it were 90% on the Week of 8/9 it would not count. Program B would count on the card at 100% for 8/9, but not towards the total at 150% for Week of 8/16. Currently the card is using a measure filtering by 95% to 105%, but it is counting how many day/program combos fell between 95% and 105%. There are 3 other cards I would like following the same idea, but showing the % of total hit, the total missing 95% and 105%, and the total missing.

 

In a bar chart, I am trying to represent that in a time range, how often is a program hitting between 95% and 105% at the weekly level. Currently the report is filtered to 10 weeks. So for example, if Program 1 falls between 95% and 105% for 6 weeks, the bar chart would show 60%. If a Program has data for only 4 weeks and fell between 95%-105%, it would show 25%. Right now, the bar chart is just breaking out the total % of Expected Hours over the defaulted 10 week time span instead of looking at it as a hit or miss on the week. I'm not sure how to approach this.

 

I did have these visualizations working by using the Summarize function on a new table, but it broke drill downs and just ended up created other problems for other visualizations. I found it also not to be the best approach because I cannot create a relationship on both Program and Date for my slicers, and also I see it not being good for long term growth of the report relationships.

 

Here is a link to an example report with more context in to my dilemna and actual data that I am working with: https://www.dropbox.com/s/11bg5gze4n03etp/Service%20Hours%20-%20PBI%20Community.pbix?dl=0

 

In sum, I am hoping someone can give me direction on these cards and ultimately educate me on how to build summarization or some sort of grouping in to measures. Its time to come to the community after hours of research. 

 

Thanks in advance!

1 Reply

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion

    funderburkj91 - This looks like a measure aggregation problem. See my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149

    The pattern is:
    MinScoreMeasure = MINX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
    MaxScoreMeasure = MAXX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
    AvgScoreMeasure = AVERAGEX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
    etc.

     

    Now, you can use SUMMARIZE or GROUPBY or SUMMARIZECOLUMNS to group but you are going to need to group in this way if you have daily information but want to report on a weekly level. So, I would recommend that you go back to your working SUMMARIZE formula and post a new thread around the problem you are having with that.

     

    I don't understand your slicer comment about relationships because your data model only has 1 data table. So, anyway I'd go that route.