Forum Discussion

a4appl-e's avatar
a4appl-e
Frequent Visitor
5 years ago
Solved

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

  • 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] ) )