cancel
Showing results for
Did you mean:
Highlighted
Regular Visitor

## Matrix with substraction in value tiles with data from slicers

Hi,

I need help with some matrix.

I've got some work to do with visualization some data, this how the part that im using there looks like:

As you can see there are some rows with different versions and 13 columns with every month and overall sum.

I have a matrix that show me sum for every country and version:

I would really like to do the matrix, which show me delta between to sums in versions than is indicated by a slider, for instance:

When I choose W11 and W12 it would show me W12-W11 in every value place, is it possible?

9 REPLIES 9
Member

## Re: Matrix with substraction in value tiles with data from slicers

You need two parameter tables, one for each slicer. Both must be identical and have to be isolated in the model. They must contain all Ws you have.

At this moment, you have to put in your matrix a measure like this:

Measure =
CALCULATE(
SUM(...)
;Version=MAX(ParameterSlicer1)
)-CALCULATE(
SUM(...)
;Version=MAX(ParameterSlicer2)
)

They are some improvements to the measure but first let's see if this works.

Best,

Jorge Bustillo.

Regular Visitor

## Re: Matrix with substraction in value tiles with data from slicers

Hi GeorgeBuster,

thank you for a quick response, I did two integer slicers and connected them to my new column [Week] which is just a number from my version. However Function CALCULATE is giving me error like this:

"A function 'MAX' has been used in a True/False expression that is used as a table filter expression. This is not allowed."

I tried it with slicers not connected but it didnt change a thing.

Is there any option to write it that it doesnt break the rules?

Member

## Re: Matrix with substraction in value tiles with data from slicers

Could you show me the measure?

One option is to introduce the boolean expression into a FILTER function.

Regular Visitor

## Re: Matrix with substraction in value tiles with data from slicers

Hi again,

I tried few things and i wrote this formulas:

Week1  Sum = SUMX(FILTER('Raw data table';'Raw data table'[Week]='Week1'[Week1 Value]);[Value]);

Week2  Sum = SUMX(FILTER('Raw data table';'Raw data table'[Week]='Week2'[Week2 Value]);[Value]);

where 'Week1' and 'Week2' are slicers.

It sums all the cells in week determined by the slicer, so its something, but now i cant even substract these 2 measures,

I want it to look like this:  new=[Week1 Value] - [Week2 Value] and it doesnt work because this measures actually dont filter the table, just slicers are filtering them. I dont know how its possible to simply calculate substraction between two filtered measures.

Member

## Re: Matrix with substraction in value tiles with data from slicers

In your measure new=[Week1 Value] - [Week2 Value], are both two columns of a table or measures? If they are columns they will need an aggregation function like MAX or MIN.

Regular Visitor

## Re: Matrix with substraction in value tiles with data from slicers

Hi,

It meant to be [Week1 Sum] and [Week2 Sum] which are my two measures, but I found out that when I try to do a Measure like that:

New=[Week1 Sum] I get the Value of [Week1 Sum] only when this measure is filtered by slicer 'Week1', when its not, I get all value Sumarised. My question is can I have a Measure that uses two slicers separately?

In Excel this looks like this:

Best Regards

Community Support Team

## Re: Matrix with substraction in value tiles with data from slicers

I think you need to use unpivot column feature to merge your month columns. Then you can simply calculate on these months to calculate month diff.

Power Query Unpivot Scenarios

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: | |
Regular Visitor

## Re: Matrix with substraction in value tiles with data from slicers

Hi v-shex-msft,

I have got something like this right now

I've got another crucial column named Country there.

What I want to do is for every country and version calculate sum of values (ideally for every month also but not nessesarily) and then substract two sums (version are indicated by two slicers) into a measure or something. Is it possible and if so, how to do it?

Community Support Team

## Re: Matrix with substraction in value tiles with data from slicers

You can create a matrix visual, year and country to rows, month to column field, value to value field.

Write a measure to use year and month to find out previous month value, you can get diff by calculate previous value with current value, then drag it to value field.

Then you can view data with year country group, current value and diff.

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: | |