Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello,
I have a request regarding the calculation of Month to Month sales growth for company and the whole market for a certain product in different cities, Here is an example.
The final report should be like this
Please note that the question is how the high lighted part is created and calculated.
I tried and found that the measures (highlighted part) can be set up on the columns, but cannot be set up on rows.
Can someone assist and explain to me?
Thanks in advance!
Thank you so much for your solution and DAX amitchandak
I still have a question regarding columns in the matrix.
This is actually the painpoint I have.
The columns should display at location level and market share level (e.g. market and company) and meantime the rows have to display the date and the difference (this month - last month)
I don't think the regular matrix can handle this situation.
And your DAX is clear and correct, very helpful. thanks again!
@PBI3000 , Create a date tanle join with you table and then have measure like
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))
diff = [MTD Sales]-[last MTD Sales]
diff % = divide([MTD Sales]-[last MTD Sales],[last MTD Sales])
and you can use option in Matrix Switch Value to rows
User | Count |
---|---|
100 | |
87 | |
81 | |
76 | |
71 |
User | Count |
---|---|
113 | |
104 | |
100 | |
73 | |
65 |