Forum Discussion
Dax
Hi, I have a card and a table visual where I want to show the correct sales value. Have been stucked for few days. Hope someone can show me the solution for this...
For the card,
if I filter BU = BU1, I should get 42 for the sales value,
if I filter Brand = A, I should get 30,
if I filter Brand = C, I should get 30,
if I filter BU = BU1 and Brand = A, I want to get 30
Currently I have a problem because in the Brand filter, I will only show Brand A, B, C, D. So in order to get the correct value for the brand (e.g. Brand C), I need to check the [Group]. If the [Group] is the same, I need to sum it up.
For the table,
I want to show 30, 12, 30, blank for Brand A, B, C, D respectively.
Sample data
| BU | Brand | Data Type | Group | Actual | Sales |
| BU1 | Brand: J01 | Red | J01 | 5 | |
| BU1 | Brand: J01 AA | Red | J01 | 10 | |
| BU1 | Brand: J01 AB | Red | J01 | 15 | |
| BU1 | Brand: J01 CC | Red | J01 | 10 | |
| BU1 | Brand A | Blue | J01 | 10 | |
| BU1 | Brand: R06 | Red | R06 | 2 | |
| BU1 | Brand: R06 AA | Red | R06 | 4 | |
| BU1 | Brand: R06 AB | Red | R06 | 6 | |
| BU1 | Brand: R06 CC | Red | R06 | 15 | |
| BU1 | Brand B | Blue | R06 | 20 | |
| BU1 | Brand C | Blue | J01 | 2 | |
| BU1 | Brand D | Blue | J08 | 9 |
Thanks,
Bee Kee
Hi Anonymous ，
First, you can create a calculated column to get only Brand A, B,C and D. Then create a measure to get the sum of sales. You can find all details in the attachment.
Nbrand = IF(IFERROR(SEARCH(":",'Sales'[Brand]),0)=0,'Sales'[Brand],BLANK())Total sales = VAR _selBrand=SELECTEDVALUE('Sales'[Nbrand]) VAR _group=CALCULATE(MAX('Sales'[Group]),FILTER('Sales','Sales'[Brand]=_selBrand)) RETURN CALCULATE(SUM('Sales'[Sales]),FILTER(ALL('Sales'),'Sales'[Group]=_group))Best Regards
Community Support Team _ Rena Ruan
If this post helps, then please consider Accept it as the solution to help the other members find it more.
3 Replies
any chance you can post some sample data (not as an image)?
Hi Anonymous ，
First, you can create a calculated column to get only Brand A, B,C and D. Then create a measure to get the sum of sales. You can find all details in the attachment.
Nbrand = IF(IFERROR(SEARCH(":",'Sales'[Brand]),0)=0,'Sales'[Brand],BLANK())Total sales = VAR _selBrand=SELECTEDVALUE('Sales'[Nbrand]) VAR _group=CALCULATE(MAX('Sales'[Group]),FILTER('Sales','Sales'[Brand]=_selBrand)) RETURN CALCULATE(SUM('Sales'[Sales]),FILTER(ALL('Sales'),'Sales'[Group]=_group))Best Regards
Community Support Team _ Rena Ruan
If this post helps, then please consider Accept it as the solution to help the other members find it more.
