Forum Discussion

John_D's avatar
John_D
Frequent Visitor
10 years ago
Solved

Pre-Aggregated Data

Hi,   I wonder whether I could get any thoughts from more experienced Power BI users on dealing with pre-aggregated data.   I'm working with results from a survey, and I have a simple hierarchy -...
  • KGrice's avatar
    KGrice
    10 years ago

    Would it be sufficient for what you're doing to use measures that report based on the count of your surveys? It doesn't solve the overall question of importing aggregated vs non-aggregated data, but maybe it will help for the particular example you used.

     

    If you've got a measure that caculates the number of responses you have:

     

    ResponseCount = DISTINCTCOUNT(ResponseTable[ResponseID])

     

    You could use that as a buffer for what you report. If you want an average of the response scores for question 1 on your survey, you could use:

    Q1Avg = IF([ResponseCount] < 10, BLANK(), AVERAGE(ResponseTable[Q1]))

     

    That will still show your rollup scores and allow you to use the drill-down features nicely, and any stores (or any level of your hierarchy, really) that have fewer than 10 responses will show up on your chart axis, but won't show any scores for that level.

KGrice's avatar
KGrice
Memorable Member
10 years ago

Would it be sufficient for what you're doing to use measures that report based on the count of your surveys? It doesn't solve the overall question of importing aggregated vs non-aggregated data, but maybe it will help for the particular example you used.

 

If you've got a measure that caculates the number of responses you have:

 

ResponseCount = DISTINCTCOUNT(ResponseTable[ResponseID])

 

You could use that as a buffer for what you report. If you want an average of the response scores for question 1 on your survey, you could use:

Q1Avg = IF([ResponseCount] < 10, BLANK(), AVERAGE(ResponseTable[Q1]))

 

That will still show your rollup scores and allow you to use the drill-down features nicely, and any stores (or any level of your hierarchy, really) that have fewer than 10 responses will show up on your chart axis, but won't show any scores for that level.

  • John_D's avatar
    John_D
    Frequent Visitor
    10 years ago

    Thank you - that is very helpful.  It wasn't (and, if I'm totally honest, still isn't) intuitively obvious that measures would behave this way - that is, although there's nothing in the statement:

     

    Q1Avg = IF([ResponseCount] < 10, BLANK(), AVERAGE(ResponseTable[Q1]))

     

    to explicitly restrict that average to just the stores within a selected district, it does it anyway.  So I've learned something very useful there.

     

    While there are some areas where I still think I need to use my own aggregates, this helps enormously.

     

    Thanks again.

     

    Cheers

    John