cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

Calculate difference between cards in fully responsive report

Good afternoon, 

 

I am trying to insert a card that calculates the difference between two existing cards. The report that I would like to show makes it possible to show the sales of four different years, which can be handpicked using a slicer. This allows users to compare the years they would like to see. 

 

To make it a bit more concrete, let's say I would like to compare the bike sales of various years (and these years can be handpicked by the user, which is why the report should be responsive in case one selects a different year). 

 

By selecting the year 2015, the cards below will show the amount of bikes sold and the sales revenue. The page basically looks like this:

 

Year (slicer) - 2015Year (slicer) - 2016Year (slicer) - 2017Year (slicer) - 2018
Sales volume 2015: 80 bikesSales volume 2016: 90 bikesSales volume 2017: 50 bikesSales volume 2018: 70 bikes
Sales revenue 2015: € 16,000Sales revenue 2016: € 18,000Sales revenue 2017: € 10,000

Sales revenue 2018: € 14,000


I would like to include cards in between these existing cards that calculate the difference between the selected years, meaning the report should look like this:

 

Year - 2015 Year - 2016 Year - 2017 Year - 2018
SV: 80+10SV: 90-40SV: 50+20SV: 70
SR: 16k+2kSR: 18k-8kSR: 10k+4kSR: 14k

 

In the future, I would also like to include multiplications using these numbers, but this is the first step towards that. Is this possible? Can anyone point me in the right direction? 

 

Thanks in advance! 

Best regards, Jan



1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: Calculate difference between cards in fully responsive report

Hi @RFC_JWK ,

 

You can create a 'What if' parameter  and modify the [Measure2].

v-lionel-msft_0-1595579889409.png

Measure 2 = 
CALCULATE(
    SUM(Sheet7[Sales]),
    FILTER(
        ALLSELECTED(Sheet7),
        Sheet7[Column] = MAX(Sheet7[Column]) && Sheet7[Year] = MAX(Sheet7[Year]) + [Parameter Value]
    )
)

v-lionel-msft_1-1595579914103.png

Please refer to my .pbix file.

 

Best regards,
Lionel Chen

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

7 REPLIES 7
Highlighted
Super User IV
Super User IV

Re: Calculate difference between cards in fully responsive report

Hi @RFC_JWK ,

 

look at this.

Technique to improve Power BI report perf by reducing visuals

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

 

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

I work at KUMAVISION AG , one of the world's largest
implementation partners for Microsoft Dynamics. #BetterTogether
"Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website

Highlighted
Community Support
Community Support

Re: Calculate difference between cards in fully responsive report

Hi @RFC_JWK ,

 

Please refer to my .pbix file.

v-lionel-msft_0-1594365562385.png

 

Best regards,
Lionel Chen

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

 

 

Highlighted
Regular Visitor

Re: Calculate difference between cards in fully responsive report

Hi @v-lionel-msft

 

Thank you for your message. This works as long as the order does not change, but not when trying to compare two non-subsequent years, which is how the data should be presented in my case. Do you know if this is possible?

 

Best regards,

Jan

Highlighted
Community Support
Community Support

Re: Calculate difference between cards in fully responsive report

Hi @RFC_JWK ,

 

For example, if you want to compare the sales value of 2015 and 2017, you can change the parameter in this formula to 2。

v-lionel-msft_0-1595324558738.png

 

Best regards,
Lionel Chen

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

Highlighted
Community Support
Community Support

Re: Calculate difference between cards in fully responsive report

Hi @RFC_JWK ,

 

Has your problem been solved?

 

Best regards,
Lionel Chen

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

Highlighted
Regular Visitor

Re: Calculate difference between cards in fully responsive report

Hi Lionel,

 

Unfortunately it has not yet been solved. This would require the user to manually change the formula when selecting different years for the comparison. 

 

Best regards,

Jan

Highlighted
Community Support
Community Support

Re: Calculate difference between cards in fully responsive report

Hi @RFC_JWK ,

 

You can create a 'What if' parameter  and modify the [Measure2].

v-lionel-msft_0-1595579889409.png

Measure 2 = 
CALCULATE(
    SUM(Sheet7[Sales]),
    FILTER(
        ALLSELECTED(Sheet7),
        Sheet7[Column] = MAX(Sheet7[Column]) && Sheet7[Year] = MAX(Sheet7[Year]) + [Parameter Value]
    )
)

v-lionel-msft_1-1595579914103.png

Please refer to my .pbix file.

 

Best regards,
Lionel Chen

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

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors