cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jfh Frequent Visitor
Frequent Visitor

Difference in Matrix instead of Total

I have problem, but I can't solve it.  I made matrix from table with values for places each year. I get Total in column to right but I need difference between column nr. 2 and 10 and ratio in next column.  I don't find solution for that.

 

My example in Matrix table

Place           2011       2012...........2019   Total(but I need difference)

East coast     10.605                  11.254    21.859 (649)

 

How do I solve this?

 

Best regards,

Jóhannes Finnur

1 ACCEPTED SOLUTION

Accepted Solutions
lmf232s Regular Visitor
Regular Visitor

Re: Difference in Matrix instead of Total

Here is a script to create a datetable.

https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

 

If you google Power BI Date Table Script you'll get a bunch of different results along with a bunch of videos that will take you step by step. 

https://www.youtube.com/watch?v=gyvhM5eiT0U

https://www.youtube.com/watch?v=KLfOOIAqfkQ

View solution in original post

6 REPLIES 6
Community Support Team
Community Support Team

Re: Difference in Matrix instead of Total

HI @jfh ,

You can use measure formula with if statement to check current row contents level and apply specific calculation formula if they are on total level and use normal summary formula on other levels. (replace original value field)

Measure Totals, The Final Word

After these steps, you can enter to 'subtotals -> column subtotals label' to custom the total level label.

19.png

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
Super User
Super User

Re: Difference in Matrix instead of Total

Hi,

See if this measure helps

=IF(HASONEVALUE(Data[Place]),[your measure],CALCULATE([your measure],LASTDATE(Calendar[Date]))-CALCULATE([your measure],FIRSTDATE(Calendar[Date])))

You shoould have a Calendar Table and an active relationship between the Date column of your Data Table to the Date column of the Calnedar Table.  In the Calendar Table, write this calculated column formula to extract the year

Year = Year(Calendar[Date])

To your visual, drag the Year field from the Calendar Table.

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Highlighted
jfh Frequent Visitor
Frequent Visitor

Re: Difference in Matrix instead of Total

Thanks, but can you show me the DAX formula.  I'm beginner in DAX

jfh Frequent Visitor
Frequent Visitor

Re: Difference in Matrix instead of Total

Hi Ashish.

Thanks.  I'm new to PBI, and just beginning using DAX.  You write: "You should have a Calendar Table and an active relationship between the Date column...."

 

I don't know how to create new table (Calendar Table).  Thats my problem (among other things.. 🙂 )

 

Best regards,

Jóhannes Finnur

lmf232s Regular Visitor
Regular Visitor

Re: Difference in Matrix instead of Total

Here is a script to create a datetable.

https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

 

If you google Power BI Date Table Script you'll get a bunch of different results along with a bunch of videos that will take you step by step. 

https://www.youtube.com/watch?v=gyvhM5eiT0U

https://www.youtube.com/watch?v=KLfOOIAqfkQ

View solution in original post

jfh Frequent Visitor
Frequent Visitor

Re: Difference in Matrix instead of Total

Thanks for your answer. In the solution to make new date table?

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (1,274)