Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
maart666
Helper I
Helper I

Subtract values from two rows in matrix

Hello,

 

Every week I get sales forecasts for which I have to compare the values. Sample model here:

Data_table.png

 

 

 

 

 

 

 

 

 

 

 

 

Matrix.png

 

 

 

 

 

 

 

 

 

 

 

PBI file 

 

I am looking for a way to show the changes between individual forecasts in quantitative and percentage terms, like in this Excel:

File 


Excel.png

 





 

 

 

 

 

Thank you in advance for any tips.

 

Marcin

1 ACCEPTED SOLUTION

Hi  @maart666 ,

 

Modify your measure as below:

Difference = 
VAR thisforecastnum =
    MIN ( Arkusz1[Forecast no] )
VAR thisforecast =
    SUM ( Arkusz1[Qty] )
var lastforecastnum=CALCULATE(MAX('Arkusz1'[Forecast no]),FILTER(ALLSELECTED(Arkusz1),'Arkusz1'[Forecast no]<MAX('Arkusz1'[Forecast no])))
VAR lastforecast =
    CALCULATE (
        SUM ( Arkusz1[Qty] ),
        Arkusz1[Forecast no] = lastforecastnum
    )
RETURN
    IF (
        ISBLANK ( lastforecast ),
        BLANK (),
        thisforecast - lastforecast
    )

 

And you will see:

 Screenshot 2020-11-16 125933.png

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

View solution in original post

5 REPLIES 5
maart666
Helper I
Helper I

Thank you Kelly,

 

works perfect!

I salut you!

 

Marcin

 

Hi Kelly,

 

it looks great accept one case, when in lastforecast there was no value (filed was empty), and in thisforecast is value, mesure do not count that diefference... Is it a way to clear that issue?

Thank you.

 

Marcin
Przechwytywanie.JPG

 

maart666
Helper I
Helper I

Thank you Pat!

It works perfectly when I compare data from subsequent forecasts. But if the forecasts are not consecutive e.g. 1 and 3 (I know I should mentioned this) the values are incorrect:

 

 

2020-11-12_07-50-15.png
Could you help solve this problem?

Thank you.

Marcin

Hi  @maart666 ,

 

Modify your measure as below:

Difference = 
VAR thisforecastnum =
    MIN ( Arkusz1[Forecast no] )
VAR thisforecast =
    SUM ( Arkusz1[Qty] )
var lastforecastnum=CALCULATE(MAX('Arkusz1'[Forecast no]),FILTER(ALLSELECTED(Arkusz1),'Arkusz1'[Forecast no]<MAX('Arkusz1'[Forecast no])))
VAR lastforecast =
    CALCULATE (
        SUM ( Arkusz1[Qty] ),
        Arkusz1[Forecast no] = lastforecastnum
    )
RETURN
    IF (
        ISBLANK ( lastforecast ),
        BLANK (),
        thisforecast - lastforecast
    )

 

And you will see:

 Screenshot 2020-11-16 125933.png

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

mahoneypat
Employee
Employee

This measure expression will give you the difference from the current forecast to the previous one.

 

Difference =
VAR thisforecastnum =
    MIN ( Arkusz1[Forecast no] )
VAR thisforecast =
    SUM ( Arkusz1[Qty] )
VAR lastforecast =
    CALCULATE (
        SUM ( Arkusz1[Qty] ),
        Arkusz1[Forecast no] = thisforecastnum - 1
    )
RETURN
    IF (
        ISBLANK ( lastforecast ),
        BLANK (),
        thisforecast - lastforecast
    )

 

To get your % difference, just change the Return part using the same variable (e.g., DIVIDE(thisforecast - lastforecast, lastforecast)

 

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors