Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have mock dataset looks like below.
On my visualization, I have two problems:
1) I am trying to show the total sales by group. - called "group total". But on my dataset, I have Item Code called "/return". I do not want to list "/return" on my visualization. So I filtered it out through filters.
I created measure called "group total" with the following dax formula :
group total = CALCULATE(SUM(Table1[Total Sales]),ALLEXCEPT(Table1,Table1[Group],Table1[Date]))
return = Calculate(Sum(Table1[Total Sales]),Filter(Table1,Find("/",Table1[Item code], ,0)<>0))
What I want eventually is something like this.
I hope I described my problem clear enough.
I would appreciate any help!
Solved! Go to Solution.
Hi @jigon ,
To update the formula as below.
return = VAR __group = MAX ( 'Table1'[Group] ) VAR _date = SELECTEDVALUE ( Table1[Date] ) RETURN CALCULATE ( SUM ( Table1[Total Sales] ), FILTER ( ALL ( Table1 ), FIND ( "/", Table1[Item code],, 0 ) <> 0 && [Group] = __group && 'Table1'[Date] = _date ) )
Regards,
Frank
Hi @jigon - Perhaps try this measure and then filter out /return in a Visual filter:
return = VAR __group = MAX('Table17'[Group]) RETURN Calculate(Sum(Table17[Total Sales]),Filter(ALL(Table17),Find("/",Table17[Item code], ,0)<>0 && [Group]=__group))
See Page 5, Table17 of attached.
@Greg_Deckler
Thank you Greg!
But I found couple of problem.
1) The group total still includes the return amount.
2) When I filter by date (using slicer) and choose February, I still see the "return" amount showing -200 and -300. But if you look at the dataset, there is no return in February.
Is there a workaround?
Thank you,
Hi @jigon ,
To update the formula as below.
return = VAR __group = MAX ( 'Table1'[Group] ) VAR _date = SELECTEDVALUE ( Table1[Date] ) RETURN CALCULATE ( SUM ( Table1[Total Sales] ), FILTER ( ALL ( Table1 ), FIND ( "/", Table1[Item code],, 0 ) <> 0 && [Group] = __group && 'Table1'[Date] = _date ) )
Regards,
Frank
Hi @jigon ,
Does that make sense? If so, kindly mark my answer as the solution to close the case please. Thanks in advance.
Regards,
Frank
User | Count |
---|---|
124 | |
106 | |
99 | |
63 | |
59 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |