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.
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?
Thank you for your help.
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.
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
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.
Regards,
Xiaoxin Sheng
Hi v-shex-msft,
thank you for your attetion.
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?
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
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |