Using Access tables and queries

[BDibuz]

I'm just starting out with Power BI, trying to use it to build a dashboard for a few Access databases I have. 

Since I already have queries in Access that sum and group the relevant information, I figured it would be as easy as telling it what goes on what axis and then letting it plot away. However, it seems that Power BI really wants to aggregate things no matter what; so even if I've already summed the revenue by week, it wants to sum it again. If I turn off summarization, it just does a count aggregation instead... which is even worse.

Am I supposed to only work with tables, and do all my querying and summing in Power BI for things to work correctly?

Also, why can't I just have it plot two un-summed sets against each other? I have a table where data is only put in weekly, so its indexed as year-weeknumber. I just want to plot this against the % values of utilization for each week, but for some reason, it just won't let me do a simple two-axis plot of two related arrays. I feel like I'm missing some fundamental knowledge here. 

[v-yulgu-msft]

Hi @BDibuz,

Agree with @MFelix.

As you mentioned above, you may need to only load original data tables, and do all querying and summing in Power BI desktop.

Regards,

Yuliana Gu

[MFelix · Accepted solution]

Hi @BDibuz,

By default PBI makes the aggregation based on the type of information you have on the column (numbers = sum, Text = count; ....).

However the way the calculations are made depends on the context of your visuals although you are having a sum if you place the correct columns on your viusal should show you only that value for the specific context.

In this case if you place the year-weeknumber as X-axis and and the values should return the correct value except if you have more than one line in your table per week, for example if you have a customer on each line so the visual will give you the sum of all the lines in that week and not of the single week value, in this case you need to add in the legend or change the type of chart and give the context of the customer.

Can you share a sample of your data and expected result?

Regards,

MFelix

[BDibuz]

Only after reading this did I realize I did indeed have two values for each Year-Week. I added the "location" tag to the legend and was then able to get a stacked column chart to display values without summing for each distinct year-week. 

Thank you!