Sumif of multiple corresponding values

[StefanoT1982]

Imagine I have this simple physical table:

CHANNEL	CUSTOMER	SALES
RETAIL	A	5
RETAIL	B	2
DISTRIBUTORS	B	2
DISTRIBUTORS	C	1

(note that CUSTOMER B has 2 different corresponding CHANNELS)

How can I create a Dax measure to get this in the pivot without having the CHANNEL column in scope in the selection?

ROWS	SALES
A	7	ALL RETAIL ROWS (5+2)
B	10	ALL RETAIL AND DISTRIBUTORS ROWS (5+2+2+1)
C	3	ALL DISTRIBUTORS ROWS (2+1)

[Kedar_Pande]

@StefanoT1982 

Sum by Customer Channels :=
VAR ThisCustomer =
SELECTEDVALUE ( Sales[CUSTOMER] )
VAR ChannelsForCustomer =
CALCULATETABLE (
VALUES ( Sales[CHANNEL] ),
Sales[CUSTOMER] = ThisCustomer
)
RETURN
CALCULATE (
SUM ( Sales[SALES] ),
KEEPFILTERS ( Sales[CHANNEL] IN ChannelsForCustomer )
)

[FBergamaschi]

Before answering you I need to understand this thing you want to create

ROWS	SALES
A	7	ALL RETAIL ROWS (5+2)
B	10	ALL RETAIL AND DISTRIBUTORS ROWS (5+2+2+1)
C	3	ALL DISTRIBUTORS ROWS (2+1)

The section ROWS should identify the Customer? Or you simply want the three nrs (7, 10 and 3) and ABC are just a way to identify the three calculations?

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

[Selva-Salimi]

Hi @StefanoT1982 ,

you can write a measure as follows:

measure channel_sales := var chnl = values (your_table [channel])

return

calculate(sum( your_table [sales]) , filter (all(your_table) , your_table [channel] in chnl))

then you can use the customer colun from your table and this measure in the visual.

If this post helps, then I would appreciate a thumbs up 👍 and mark it as the solution ✔️ to help the other members find it more quickly.

[jgeddes · Accepted solution]

Maybe this measure will work for you.

Measure = 
var _chTable = 
SELECTCOLUMNS('Table',"__channel", [CHANNEL])
RETURN
SUMX(
    FILTER(ALL('Table'), 'Table'[CHANNEL] IN _chTable),
    [SALES]
)