Dax

[Anonymous]

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

[PaulDBrown]

@any chance you can post some sample data (not as an image)?

[Anonymous]

Hi Paul, I'm not sure how I can attach my excel file so I created a table within the post.

[Anonymous · Accepted solution]

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.