Forum Discussion
Sumif of multiple corresponding values
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) |
Maybe this measure will work for you.
Measure = var _chTable = SELECTCOLUMNS('Table',"__channel", [CHANNEL]) RETURN SUMX( FILTER(ALL('Table'), 'Table'[CHANNEL] IN _chTable), [SALES] )
4 Replies
jgeddesSuper User
Maybe this measure will work for you.
Measure = var _chTable = SELECTCOLUMNS('Table',"__channel", [CHANNEL]) RETURN SUMX( FILTER(ALL('Table'), 'Table'[CHANNEL] IN _chTable), [SALES] )- Selva-Salimi
Solution Sage
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.
- 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
- Kedar_Pande
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 )
)
