cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Mauricioff
New Member

Dynamically compare measures

Hi all


Simply put - I need to dinamically compare (percentage between the two) two different matrix tables with measures on it as I change the period. Have wandered through a variety of topics searching for a solution and the closest I get is this one but don't really address the fact I need to compare between different measures: https://community.powerbi.com/t5/Community-Blog/Dynamically-Compare-the-Value-of-Two-Periods/ba-p/15...

I'm relatively new to PBI too so I might have overlooked an existing topic - I apologize in advance!

I have two matrix tables looking exactly the same with the same filters applied to it - except for the period. There's around 15 different measures (expenses per kilo) hence I think there has to be a smart way to do it.

The matrix tables look a little bit like this:

PeriodJan
  
Expense 1 per kilo<value>
Expense 2 per kilo<value>
Expense 3 per kilo<value>

 

PeriodFeb
  
Expense 1 per kilo<value>
Expense 2 per kilo<value>
Expense 3 per kilo<value>

 

And my database looks like this:

PeriodAttributeValueStateSupplier
JanExpense 150FloridaABC
JanExpense 260FloridaABC
JanTotal kilograms15FloridaABC


In this case I'd like to compare Feb to Jan and whatever period I filter there.

 

Thanks in advance!

1 ACCEPTED SOLUTION
v-robertq-msft
Community Support
Community Support

Hi, @Mauricioff 

According to your description, I can roughly understand your requirement, I think you can achieve this using Matrix and “Edit interactions”, you can try my steps:

  1. Create two same slicers and two same matrices, place them like this:

v-robertq-msft_0-1620182698178.png

 

  1. Go to the “Edit interactions”, set the interaction for the two slicers like this:

v-robertq-msft_1-1620182698181.png

v-robertq-msft_2-1620182698184.png

v-robertq-msft_3-1620182698186.png

 

And you can compare the data as what you want, like this:

v-robertq-msft_4-1620182698187.png

 

You can download my test pbix file below

 

If this result is not what you want, you can post some sample data(without sensitive data) and your expected result.

How to Get Your Question Answered Quickly 

Thank you very much!

 

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
v-robertq-msft
Community Support
Community Support

Hi, @Mauricioff 

According to your description, I can roughly understand your requirement, I think you can achieve this using Matrix and “Edit interactions”, you can try my steps:

  1. Create two same slicers and two same matrices, place them like this:

v-robertq-msft_0-1620182698178.png

 

  1. Go to the “Edit interactions”, set the interaction for the two slicers like this:

v-robertq-msft_1-1620182698181.png

v-robertq-msft_2-1620182698184.png

v-robertq-msft_3-1620182698186.png

 

And you can compare the data as what you want, like this:

v-robertq-msft_4-1620182698187.png

 

You can download my test pbix file below

 

If this result is not what you want, you can post some sample data(without sensitive data) and your expected result.

How to Get Your Question Answered Quickly 

Thank you very much!

 

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

amitchandak
Super User IV
Super User IV

@Mauricioff , 1. if want to period one period with another standard period, then you can use time intelligence

 

 

examples

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]))
next month Sales = CALCULATE(SUM(Sales[Sales Amount]),nextmonth('Date'[Date]))
this month =MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH('Date'[Date])))
last MTD (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
previous month value = CALCULATE(sum('Table'[total hours value]),previousmonth('Date'[Date]))

 

 

this can 2 months behind or 3.

 

2. But you want to select two 2 periods and compare, you need an additional date table to refer

How to use two Date/Period slicers: https://youtu.be/WSeZr_-MiTg

 

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Hi amitchandak

Thanks for the reply.

 

Apparently I can't escape the fact I will need to create one additional measure for comparing different periods. In your first proposed solution, I'd need to create those additional measures for all of my 15 measures, is that right?

 

I have around ~15 different measures (Expense X per kilo) which I need to compare from one period to another.

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Urdu Hindi D365 Bootcamp 768x460.png

Urdu Hindi D365 Bootcamp

Dont miss our very own April Dunnam’s The Developer Guide to the Galaxy! Find out what the Power Platform has to offer for the traditional developer.

Top Solution Authors
Top Kudoed Authors