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 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
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 |
---|---|
115 | |
99 | |
88 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |