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.
I have created the following table using the Matrix object:
I would like to compute ("Value in Use" - "Carrying Amount") for each column e.g. the yellow parts in 2013, and display that difference where the orange blocks are. Example for 2013 in first category (EU/LA) - 4,552.27 - 3,688.77 = 863,5. the result should appear in the orange part directly under 4,552.27. This for each column.
Is this possible within the Matrix construct? Or would it be better to compute these differences in the Power Query parts as I read the data in? I currently have the data in a normal table, with each row containing either a Carrying Amount or a Value in Use.
EDIT: here is some (space separated) test data that I have been using...
BestF CapGrad Type Year Value
Tesla S/U Car 2016 1050.753275
Tesla S/U Car 2015 13.76775538
Tesla S/U Value 2016 2133.461169
Tesla S/U Value 2015 1785.887931
Tesla Mercedes Car 2016 154.0025548
Tesla Mercedes Car 2015 133.162
Tesla Mercedes Value 2016 243.5816935
Tesla Mercedes Value 2015 100.9351563
Tesla Mercedes + GC/NA Car 2016 688.2506933
Tesla Mercedes + GC/NA Car 2015 378.4544428
Tesla Mercedes + GC/NA Value 2016 879.0503796
Tesla Mercedes + GC/NA Value 2015 464.0001573
Tesla GC/NA + SFR Car 2016 100.991353
Tesla GC/NA + SFR Car 2015 29.6827127
Tesla GC/NA + SFR Value 2016 1169.492568
Tesla GC/NA + SFR Value 2015 394.1421524
Tesla Life Car 2016 48.02338352
Tesla Life Car 2015 102.5789022
Tesla Life Value 2016 28.83309994
Tesla Life Value 2015 274.9924524
First S/U Car 2016 193.3435293
First S/U Car 2015 22.18691285
First S/U Value 2016 57.35636377
First S/U Value 2015 91.61001358
First I/O Car 2016 4.888315691
First I/O Car 2015 110.4481171
First I/O Value 2016 141.8388878
First I/O Value 2015 168.2045724
First L/G Car 2016 64.43360298
First L/G Car 2015 107.0808728
First L/G Value 2016 26.6170231
First L/G Value 2015 60.10688546
First S/U Value 2016 193.173096
Tesla S/U Value 2016 381.6880641
Tesla Mercedes Value 2016 136.888348
Tesla Mercedes + GC/NA Value 2016 1615.797186
Tesla GC/NA + SFR Value 2016 876.8585662
Tesla Life Value 2016 452.176229
Solved! Go to Solution.
Hi @nimitchell,
Please check below formula if it works on your side.
Measure:
Diff = IF ( COUNTROWS ( 'Sample data' ) = COUNTROWS ( FILTER ( ALL ( 'Sample data' ), [CapGrad] = MAX ( 'Sample data'[CapGrad] ) ) ) || COUNTROWS ( 'Sample data' ) = COUNTROWS ( FILTER ( ALL ( 'Sample data' ), [CapGrad] = MAX ( 'Sample data'[CapGrad] ) && [Year] = MAX ( 'Sample data'[Year] ) ) ), ABS ( CALCULATE ( SUM ( 'Sample data'[Value] ), 'Sample data'[Type] = "Car" ) - CALCULATE ( SUM ( 'Sample data'[Value] ), 'Sample data'[Type] = "Value" ) ), SUM ( 'Sample data'[Value] ) )
Above formula only available on marking part, I haven't added any conditional to affect total column and total row.
Regards,
Xiaoxin Sheng
HI @nimitchell,
Can you please provide some sample data to test?
For your scenario, you should write a measure to instead the original amount column and add condition to check the subtotal row.
Sample:
SubDiff = IF ( COUNTROWS ( 'Table' ) = COUNTROWS ( FILTER ( ALL ( 'Table' ), [Date].[Month] = MAX ( [Date].[Month] ) ) ) || COUNTROWS ( 'Table' ) = COUNTROWS ( FILTER ( ALL ( 'Table' ), [Date].[Month] = MAX ( [Date].[Month] ) && [Date].[Year] = MAX ( [Date].[Year] ) ) ), ABS ( CALCULATE ( SUM ( 'Table'[Amount] ), 'Table'[Type] = "b" ) - CALCULATE ( SUM ( 'Table'[Amount] ), 'Table'[Type] = "a" ) ), SUM ( 'Table'[Amount] ) )
Regards,
Xiaoxin Sheng
Thank you very much for the great answer. I have attempted to adapt it to my data but still have a few issues (the table is not showing as expected, even though the measure.
I have included data below that has the same structure as mine - could you help me adapt your solution please?
CapGrad is the columns, so split by S/U, Mercedes, etc., whicha re in turn drilled down into the Type (like your 'a' and 'b'. The columns are years. The difference I want to compute is then the corresponding Value for a set of CapGrad and Type in one year.
If you copy and paste into excel using tab as a delimiter it works. I will try to uplaod it another way too just in case that isn't helpful.
BestF CapGrad Type Year Value Tesla S/U Car 2016 1050.753275 Tesla S/U Car 2015 13.76775538 Tesla S/U Value 2016 2133.461169 Tesla S/U Value 2015 1785.887931 Tesla Mercedes Car 2016 154.0025548 Tesla Mercedes Car 2015 133.162 Tesla Mercedes Value 2016 243.5816935 Tesla Mercedes Value 2015 100.9351563 Tesla Mercedes + GC/NA Car 2016 688.2506933 Tesla Mercedes + GC/NA Car 2015 378.4544428 Tesla Mercedes + GC/NA Value 2016 879.0503796 Tesla Mercedes + GC/NA Value 2015 464.0001573 Tesla GC/NA + SFR Car 2016 100.991353 Tesla GC/NA + SFR Car 2015 29.6827127 Tesla GC/NA + SFR Value 2016 1169.492568 Tesla GC/NA + SFR Value 2015 394.1421524 Tesla Life Car 2016 48.02338352 Tesla Life Car 2015 102.5789022 Tesla Life Value 2016 28.83309994 Tesla Life Value 2015 274.9924524 First S/U Car 2016 193.3435293 First S/U Car 2015 22.18691285 First S/U Value 2016 57.35636377 First S/U Value 2015 91.61001358 First I/O Car 2016 4.888315691 First I/O Car 2015 110.4481171 First I/O Value 2016 141.8388878 First I/O Value 2015 168.2045724 First L/G Car 2016 64.43360298 First L/G Car 2015 107.0808728 First L/G Value 2016 26.6170231 First L/G Value 2015 60.10688546 First S/U Value 2016 193.173096 Tesla S/U Value 2016 381.6880641 Tesla Mercedes Value 2016 136.888348 Tesla Mercedes + GC/NA Value 2016 1615.797186 Tesla GC/NA + SFR Value 2016 876.8585662 Tesla Life Value 2016 452.1762297
Hi @nimitchell,
Please check below formula if it works on your side.
Measure:
Diff = IF ( COUNTROWS ( 'Sample data' ) = COUNTROWS ( FILTER ( ALL ( 'Sample data' ), [CapGrad] = MAX ( 'Sample data'[CapGrad] ) ) ) || COUNTROWS ( 'Sample data' ) = COUNTROWS ( FILTER ( ALL ( 'Sample data' ), [CapGrad] = MAX ( 'Sample data'[CapGrad] ) && [Year] = MAX ( 'Sample data'[Year] ) ) ), ABS ( CALCULATE ( SUM ( 'Sample data'[Value] ), 'Sample data'[Type] = "Car" ) - CALCULATE ( SUM ( 'Sample data'[Value] ), 'Sample data'[Type] = "Value" ) ), SUM ( 'Sample data'[Value] ) )
Above formula only available on marking part, I haven't added any conditional to affect total column and total row.
Regards,
Xiaoxin Sheng
Could you please explain how this solution is actually working? Perhaps with some comments in the code?
Hi @nimitchell,
By default, matrix visual will auto summary values. Actually, my conditions also based on it.
It will check on current matrix summary result and manually filter result to apply the custom calculation on specific level.
For example:
Test 2 = IF ( COUNTROWS ( 'Sample data' ) = COUNTROWS ( FILTER ( ALL ( 'Sample data' ), [CapGrad] = MAX ( 'Sample data'[CapGrad] ) && [Year] = MAX ( 'Sample data'[Year] ) ) ), "Custom" , SUM ( 'Sample data'[Value] ) )
If your visual contians some filter on it, you should use allselected to instead the all function, it will apply on current filter.
Wrong:
Right:
Regards,
Xiaoxin Sheng
Thanks for the adaption, but unfortunately it doesn't work for me... I don't think it can be due to my data being slightly different - the structure is identical, only the names and values are altered and I of course used my correct names when applying your measure.
It seems as thought the IF statement is not getting into the interesting part of computing the difference. The table still simply shows the sum of 'Car' and 'Value', not the difference. Could you maybe explain a little bit of the logic behind your solution? Perhaps I can the adapt it myself. Why do you filter in the IF condition using: `[CapGrad] = MAX ( 'Sample data'[CapGrad] )`?
CapGrad is a text column, and the MAX function says that it ignores text.
EDIT:
Your measure does work as desired! Thank you! My problem was, well, me. I had a filter missing, meaning there was an extra value in Type (not just the two I had given you in my data sample). It would still be useful for my learning if you could explain a few of the steps in your solution 🙂 Thanks again!
p.s - I do not need the extra condition for the Totals column 🙂
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 |
---|---|
98 | |
97 | |
81 | |
77 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |