Forum Discussion
Filter the contents inside MATRIX visual
Actually, thinking about it, the average method could potentially not achieve what you are looking for.
If you have a set of values for a year like {30, 29, 30, 31}, the average method will hide both 30 values.
The following method however will work: compare the minimum value in a set to the maximum value. If they are the same, all values are the same...
So...
No duplicate profits = VAR _Min = MINX ( ALL ( 'Warehouse table'[Warehouse] ), [Your measure] ) VAR _MAX = MAXX ( ALL ( 'Warehouse table'[Warehouse] ), [Your measure] ) RETURN IF ( AND ( ISINSCOPE ( 'Warehouse table'[Warehouse] ), ISINSCOPE ( 'Year Table'[Year] ) ), IF ( _Min = _Max, BLANK (), [Your measure] ) )PS. I tried deleting the previous message but the forum won't let me for that particular post for some reason....
Here is one way. Basically you compare the sum (profit) of each warehouse with the average for all warehouses in a year.
First the model:
The calculation (without totals)
No duplicate profits =
VAR _Profit =
SUM ( fTable[Profit] )
VAR _Average =
CALCULATE ( AVERAGE ( fTable[Profit] ), ALL ( 'Warehouse table'[Warehouse] ) )
RETURN
IF (
AND (
ISINSCOPE ( 'Warehouse table'[Warehouse] ),
ISINSCOPE ( 'Year Table'[Year] )
),
IF ( _Profit = _Average, BLANK (), _Profit )
)
and if you need totals:
No Dups with totals =
SUMX(SUMMARIZE(fTable, 'Warehouse table'[Warehouse], 'Year Table'[Year]), [No duplicate profits])
Sample PBIX attached
Hi Paul
Thank you for your reply .
I find the logic in your solution excellent but as I mentioned before, the Profit value is a measured value that can't be calculated in the average function !!!
looking forward to your reply
REgards
My apologies. I did see you mention it and then promptly forgot about it in the solution.
This measure will work:
No duplicate profits = VAR _Profit = [Your measure] VAR _Average = AVERAGEX ( ALL ( 'Warehouse table'[Warehouse] ), [Your measure] ) RETURN IF ( AND ( ISINSCOPE ( 'Warehouse table'[Warehouse] ), ISINSCOPE ( 'Year Table'[Year] ) ), IF ( _Profit = _Average, BLANK (), _Profit ) )Actually, thinking about it, the average method could potentially not achieve what you are looking for.
If you have a set of values for a year like {30, 29, 30, 31}, the average method will hide both 30 values.
The following method however will work: compare the minimum value in a set to the maximum value. If they are the same, all values are the same...
So...
No duplicate profits = VAR _Min = MINX ( ALL ( 'Warehouse table'[Warehouse] ), [Your measure] ) VAR _MAX = MAXX ( ALL ( 'Warehouse table'[Warehouse] ), [Your measure] ) RETURN IF ( AND ( ISINSCOPE ( 'Warehouse table'[Warehouse] ), ISINSCOPE ( 'Year Table'[Year] ) ), IF ( _Min = _Max, BLANK (), [Your measure] ) )PS. I tried deleting the previous message but the forum won't let me for that particular post for some reason....
Great work
Highly appricated