Forum Discussion
How to get difference between two columns in Matrix based on slicer selection of Year in Power Bi
Hi Anonymous
Here you go https://www.dropbox.com/t/sMyzwonHckNHvdKX
Original Measure code remains the sameMeasure = COUNTROWS ( DISTINCT ('Table'[Value] ) )New Measure code is
Measure1 = VAR MaxYear = MAX ( 'Table'[Year] ) VAR MinYear = MIN ( 'Table'[Year] ) VAR MaxYearValue = CALCULATE ( [Measure], 'Table'[Year] = MaxYear ) VAR MinYearValue = CALCULATE ( [Measure], 'Table'[Year] = MinYear ) VAR Difference = MaxYearValue - MinYearValue VAR Result = SWITCH ( TRUE, NOT HASONEVALUE ( 'Table'[Year] ), Difference, COUNTROWS ( ALLSELECTED ('Table'[Year] ) ) = 1, BLANK(), [Measure] ) RETURN Result
Community ChampionHi Anonymous
Check out this sample file https://www.dropbox.com/t/34kVadAA9Yb40CUO
Actually, you cannot add a new measure to the matrix visual. If you do so you will see two values under each year. Therefore, the only choice is to create a new measure that is built on top of the original one, playing with the code in order to view the difference at the Total column like this
This is the code: Note: [Measure] is your original measure
Measure1 =
VAR MaxYear = MAX ( 'Table'[Year] )
VAR MinYear = MIN ( 'Table'[Year] )
VAR MaxYearValue =
CALCULATE (
[Measure],
'Table'[Year] = MaxYear
)
VAR MinYearValue =
CALCULATE (
[Measure],
'Table'[Year] = MinYear
)
VAR Difference = MaxYearValue - MinYearValue
VAR Result =
SWITCH (
TRUE,
NOT HASONEVALUE ( 'Table'[Year] ), Difference,
COUNTROWS ( ALLSELECTED ('Table'[Year] ) ) = 1, BLANK(),
[Measure]
)
RETURN
Result
Thanks For Your Solution .
One thing is the values column in your Pbix is numbers like 10 , 20 26 ,but my requirement is, values column is codes like b010 , c350, d6f , ng6 . I categorized them as Distinct count . So they look like numbers in my attached file . I think the DAX is almost correct . Can you modify dax by using my scenario .
Thanks For such great Help .- tamerj1
You can just refer your measure name instead of [Measure] and it should work. Otherwise, you can send your file with a sample data and I'll do it for you.
If you are satisfied please mark my reply as accepted solution. Kudoes are also appreciated.
I replaced values with codes . My requirement is look like this . I categorized them as distinct count ( from column tools ) .What I want is exactly like your output . Measure 1 is exactly corect . I think we need to modify the Measure , with codes (Distinct Count ) .Please modify the dax .,It would work fine. Am unable tho send the file .
Kudos !
Thanks for such quick response