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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
Anonymous
Not applicable

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.

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.

Anonymous
Not applicable

 

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

Hi @Anonymous,

 

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
If this post helps, please consider accept as solution to help other members find it more quickly.
Anonymous
Not applicable

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?

HI @Anonymous,

 

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
If this post helps, please consider accept as solution to help other members find it more quickly.
GeorgeBuster
Advocate III
Advocate III

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.

Anonymous
Not applicable

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?

Hi Radkos,

 

Could you show me the measure?

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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