cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Radkos Regular Visitor
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:screen_matrix_help.PNG

 

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:

screen_matrix_help1.5.PNG

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:screen_matrix_help2.PNG

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

Thank you for your help.

 

9 REPLIES 9

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

Hi Radkos,

 

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.

Radkos Regular Visitor
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?

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

Hi Radkos,

 

Could you show me the measure?

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

Radkos Regular Visitor
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.

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

Hi Radkos,

 

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.

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

screen_help.PNG

Best Regards

Community Support Team
Community Support Team

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

Hi @Radkos,

 

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

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

Hi v-shex-msft,

thank you for your attetion.

I have got something like this right now

halp_4.PNG

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
Community Support Team

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

HI @Radkos,

 

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