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 all,
I have a question : is there any options to use totals from a matrix to calculate a difference? I want to use the totals from matrix, to make a difference between years.
So, in the example below i have the amounts and I want to use them to make a difference between all the totals from matrixs.
Thank you
Solved! Go to Solution.
Hi @Gheb_Gabriela ,
For this you need to redo the formula you use for the matrix values. I have used the following data for example:
Now add the following measure to your dataset:
Adjusted Value =
IF (
HASONEVALUE ( 'Table'[Cat] ),
SUM ( 'Table'[Value] ),
SUM ( 'Table'[Value] )
- CALCULATE (
SUM ( 'Table'[Value] ),
FILTER (
ALL ( 'Table'[Year], 'Table'[Quarter], 'Table'[Time] ),
'Table'[Year]
= SELECTEDVALUE ( 'Table'[Year] ) - 1
&& 'Table'[Quarter] = SELECTEDVALUE ( 'Table'[Quarter] )
)
)
)
Check PBIX file attach.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @Gheb_Gabriela ,
For this you need to redo the formula you use for the matrix values. I have used the following data for example:
Now add the following measure to your dataset:
Adjusted Value =
IF (
HASONEVALUE ( 'Table'[Cat] ),
SUM ( 'Table'[Value] ),
SUM ( 'Table'[Value] )
- CALCULATE (
SUM ( 'Table'[Value] ),
FILTER (
ALL ( 'Table'[Year], 'Table'[Quarter], 'Table'[Time] ),
'Table'[Year]
= SELECTEDVALUE ( 'Table'[Year] ) - 1
&& 'Table'[Quarter] = SELECTEDVALUE ( 'Table'[Quarter] )
)
)
)
Check PBIX file attach.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em Português@MFelix Thanks for your solution, it's working for what I need, except that i have blanks instead 0 and everything i've tried it's not working. I have used on your formula +0. Maybe in the pbix that you have attached, you can show me a sollution? To can replace blanks in the matrix with 0?
Have a great day!
Hi @Gheb_Gabriela ,
What do you mean by you have blanks instead of 0? In my model the values are also blanks and not 0. Can you please provide further insight on that part?
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @MFelix ,
Yes, I know, but your example is just an example, for my report because of the blanks from matrix, if I want to filter only Q1FY19 and Q2FY19, those values with 0 means that i will be not able to see also all the others category ( as is in your example A,B) and i need them to be there even they have 0 value, for my company.
Do you understand what i mean?
Thanks! Appreciate your quick reply
You mean that the matrix lines can be blank correct liken the image below the category dissapears:
In this case best option is to select the visual and on the line values select the option Show items with no Data:
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em Português@MFelix i've tried this solution from the begging, but no... it's not showing "0". Maybe, it's from my database... the column with the values should be formatted different or something like this.
Thanks
Try to rearrange the measure to:
Adjusted Value =
IF (
HASONEVALUE ( 'Table'[Cat] ),
SUM ( 'Table'[Value] ) + 0,
SUM ( 'Table'[Value] )
- CALCULATE (
SUM ( 'Table'[Value] ),
FILTER (
ALL ( 'Table'[Year], 'Table'[Quarter], 'Table'[Time] ),
'Table'[Year]
= SELECTEDVALUE ( 'Table'[Year] ) - 1
&& 'Table'[Quarter] = SELECTEDVALUE ( 'Table'[Quarter] )
)
)
)
The +0 is only on the first part of the IF statement,
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCovering 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 |
---|---|
115 | |
100 | |
89 | |
68 | |
61 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |