After using this amazing forum, books, Reddit and Discord making a lot of progress on my current work project as a total newbie. I am currently stuck on a specific issue! I was able to manipluate all my data in power query so I can easy access over 60 different Excel files and it's very clean data now.
Now the issue is making that data work the way it's meant to be. Growth Trends and not Totals. Which is what is currently defaulting as I am obviously not setting it up right. I have 3 years worth of data with 12 months in each year. Power Query for the win! My current issue is when I create graphs It combines the months for each grouping as if they are a total sales report. I am doing a Membership Growth Trend and combining my numbers blows up my entire report.
As an example, a specific group has 40 people for 4 months but no gains then it would be 40 for each month. However if I show the growth of a year it totals those. Thinking the 40 is 40 new people..... But I do need the solid numbers to show how many we gained for all my groups in a specific month.
How do I control this and make sure it's easy to work with? I assume I could make an entire new system using power query but I hope there is a different way because I already have to transform and manipulate a lot of data to get it clean as it's a raw report and that seems like a rabbit hole.
And because of that none of my Membership Trends are actually correct Please help so this poor soul can sleep again and not have to worry about work deadlines. While I'm mostly doing Line Graphs here is a more easily understandable representation of my issue.
- And because of that issue none of my Membership Trends are actually correct.
- as it's totaling the numbers of each month which actually represent "current" numbers of that specific month
1 ACCEPTED SOLUTION
I am now confused? Are you trying to get the difference between same month last year and current month or the previous month vs current month? If it is the latter, a simple CALCULATE( <aggregation measure>, PREVIOUSMONTH (Dates[Date]) ) will do.
Hi @danextian
So the good news is your question made me re evaluate how I was tackling this and managed to solve the issue by googling some different terms and learning about Drills. Which solved the problem. That and my sample file for a transform function were infecting my data as I forgot to remove it.
I guess my question now is about the same, but instead of the graph how can I make my matrix not total everything as it makes it confusing and incorrect at a glance. Granted if you look at each one it's fine but it makes it look like it's representing totals instead of membership changes.
So this Data set from my work reports is what I am pulling from but I can only snag a single month and I'm unable to get a full file. So I was able to download a CSV of each month. Below is the single month format that is showing correctly. Basically I want to replicate this but instead have it show all the months per year not just the single month.
I am having a similar issue with Pie chart graphs as I can't split it from summarizing each month as a giant total. Any suggestions?
The yellow alone is way more then our total as it is adding each months totals. (Trying to show the difference between groups of growth and current numbers)
I am now confused? Are you trying to get the difference between same month last year and current month or the previous month vs current month? If it is the latter, a simple CALCULATE( <aggregation measure>, PREVIOUSMONTH (Dates[Date]) ) will do.
