Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all,
I am trying to figure out how to group sales data by the year based on what type of review it has gone through. My issue is I don't know how to account for a range of years, if the review spans more than just one year how can I assoicate the sales data with it correctly.
Group Type | Sales | Sales Year | Review Start Date | Review End Date | Review Type |
1 | 2014 | 5000 | 2014 | 2016 | A |
2 | 2015 | 6000 | 2010 | 2013 | B |
3 | 2016 | 7000 | 2014 | 2017 | C |
4 | 2015 | 8000 | 2014 | 2016 | A |
5 | 2016 | 9000 | 2015 | 2016 | A |
In this example I would expect the following results for the time period 2014-2017 which I have set with a splicer.
Total Sales | 35000 |
Review Type A | 22000 |
Review Type B | 0 |
Review Type C | 7000 |
Not Reviewed | 6000 |
Here the total sales is just the sum of the sales which they all fall betwen 2014 and 2017. Then you have sales for Review Type A that fall on the first review date, inbetween, and the final year. Review Type B has no sales becuase the review took place in a time frame prior to the sales year.
I would like to be able to group by Group Type and have totals for each Review Type as my final results as the Group Types will repeat in the full data set. In the full data you would end up with Group Type 1-xxx and the following for each Group Type: Total Sales, Total for Review A, B, C and Total Not Reviewed.
Thank you for the help!
You can use the following DAX to get a new table group by Review Type. Then you can use it to get the similar information as you expected. I did not find a way that could merge Not Reviewed information in to Review Type table. Maybe someone else could help.
ReviewSummary = GROUPBY(Review,Review[Review Type],"SalesSum",SUMX(CURRENTGROUP(),IF(Review[Review End Date]>=Review[Sales],Review[Sales Year],0)))
Thank you for the formula. I was able to replicate it for my data. I think I wasn't very clear on needing the Group Type as well, I didn't put it in my example solution like I should have.
I would like to also include the Group Type. In a perfect world the solution would look like the following:
For 2014-2017 | |||||
Group Type | Total Sales | Review Type A | Review Type B | Review Type C | Not Reviewed |
1 | 5000 | 5000 | 0 | 0 | 0 |
2 | 6000 | 0 | 0 | 0 | 6000 |
3 | 7000 | 0 | 0 | 7000 | 0 |
4 | 8000 | 8000 | 0 | 0 | 0 |
5 | 9000 | 9000 | 0 | 0 | 0 |
I can also just caculate the Not Reviewed column by doing a subtraction of Total Sales - All Review Types but I wasn't sure how to get table created in this format for a larger data set.
Thanks!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
97 | |
82 | |
75 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |