cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Difference and index column in matrix with slicer

Hi,

 

I would like to create a measure which calculate the difference of 2 columns in a matrix and chance according to the slicer selections.

I have read some other topics, however it seems very specific for every case and I can't find a solution for mine.

 

My report page:

voorbeeld difference table.PNG

 

The data which forms the numbers looks as follows:

Customerproduct groupproductdateValue
10023101015406.7.201819,5
100231010154015.7.201858,5
10023101025606.7.201819,5

 

Now I would like to add 2 columns that calculate the difference of the numbers in the table.

As follows:

20172018DifferenceIndex
10836797,2412500320,781663523,54115,4
779662,79789100,609437,81101,2
994985,291058116,8863131,59106,3
278138,70325577,8747439,17117,1
6531291,036597573,4266282,39101,0

 

Difference = 2018 - 2017

Index = 2018 / 2017

 

If any questions please ask.

Hope someone can help me out.

 

Kind regards,

Guido

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
sokg Established Member
Established Member

Re: Difference and index column in matrix with slicer

I saw you have a calendar table

 

Create these measures:

 

SumofValue = Sum(Table[Value])

SumOfValuePrevYear = Calculate([SumofValue];SAMEPERIODLASTYEAR(Calendar[date])

Difference = [SumofValue] - [SumOfValuePrevYear]

2018/2017 = [SumofValue] / [SumOfValuePrevYear]

 

Hope this help.

5 REPLIES 5
Highlighted
sokg Established Member
Established Member

Re: Difference and index column in matrix with slicer

I saw you have a calendar table

 

Create these measures:

 

SumofValue = Sum(Table[Value])

SumOfValuePrevYear = Calculate([SumofValue];SAMEPERIODLASTYEAR(Calendar[date])

Difference = [SumofValue] - [SumOfValuePrevYear]

2018/2017 = [SumofValue] / [SumOfValuePrevYear]

 

Hope this help.

Anonymous
Not applicable

Re: Difference and index column in matrix with slicer

Hi @sokg

 

Thank you very much for your reply

 

It works, however I get the difference 2 times:

 

index en difference voorbeeld.PNG

 

Do you have an idea how to fix this?

 

Kind regards,

Guido

sokg Established Member
Established Member

Re: Difference and index column in matrix with slicer

Try select one year only (i.e. 2018)

 

Anonymous
Not applicable

Re: Difference and index column in matrix with slicer

Hi @sokg

 

That would be an option, however the desired format would be:

20172018DifferenceIndex
10836797,2412500320,781663523,54115,4
779662,79789100,609437,81101,2
994985,291058116,8863131,59106,3
278138,70325577,8747439,17117,1
6531291,036597573,4266282,39

101,0

 

Any idea how we could create this?

 

Regards

Anonymous
Not applicable

Re: Difference and index column in matrix with slicer

Hi @sokg

 

Found a solution. Made 2 seperate measures in stead of using "Year" in Kalender table

 

Thanks again for your help!