Forum Discussion
Need help with a DAX formula
I have a sales table with sales amount grouped by account id that is linked to an accounts dimension. I now have to add KPI table, which has a relation with accounts table one to many.
Sales => Accounts <= KPI
KPI table is nothing but information about the KPI Name and accounts to use for that calculation via Sales Amount and has a multipler, that tells me how to use the account, +1 / - 1.
When I try to create a Sales Amount like,
SUMX( Sales, Sales[Amount] * RELATED( KPI[Multiplier] ) ), it doesn't work. So I was wondering if there is a way I can get this to working.
I am currently able to duplicate the account data at KPI level and load that information directly after using the multiplier. I am able to move ahead, but that took my record count from 4 Million to 30 million. I am just wondering, what would be the better and correct way to do it, because if my KPI's increase in the future, I will have to reload the data which feels bad.
Thanks in advance
a4appl-e , You can not use related in this case, Try a measure like
SUMX( Values(Account[Account]), sum(Sales[Amount]) * Max( KPI[Multiplier] ) )
or
SUMX( Values(Account[Account]), sum(Sales[Amount]) * sum( KPI[Multiplier] ) )
1 Reply
- amitchandak
Super User
a4appl-e , You can not use related in this case, Try a measure like
SUMX( Values(Account[Account]), sum(Sales[Amount]) * Max( KPI[Multiplier] ) )
or
SUMX( Values(Account[Account]), sum(Sales[Amount]) * sum( KPI[Multiplier] ) )