Forum Discussion

PBI3000's avatar
PBI3000
Frequent Visitor
3 years ago

Display Measures on Each Row in Matrix

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!

 

 

General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    3 years ago

    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

     

     

  • PBI3000's avatar
    PBI3000
    Frequent Visitor
    3 years ago

    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!