Forum Discussion

apohl1's avatar
apohl1
Icon for Helper II rankHelper II
4 years ago
Solved

Intersect formula to sum up values

Hi all,   I'm building a model that does account segmentation based on accounts historical sales performance. The account segmentation is a measure, it's called Account status and the results are "...
apohl1's avatar
apohl1
Icon for Helper II rankHelper II
4 years ago
Here is how I currently sum up the value of the matching results but the formula is very slow, intersect seems to be much faster.
 
Account status growth = SWITCH (
SELECTEDVALUE ( 'Account status'[Account status] ),
"Gained",
CALCULATE (
[Growth],FILTER ('Account_Status_Table_Segment',[Account status segment] = "Gained")),
"Gaining",
CALCULATE (
[Growth],FILTER ('Account_Status_Table_Platform',[Account status platform] = "Gaining")),
"Stable",
CALCULATE (
[Growth],FILTER ('Account_Status_Table_Platform',[Account status platform] = "Stable")),
"Losing",
CALCULATE (
[Growth],FILTER ('Account_Status_Table_Product',[Account status product] = "Losing")),
"Lost",
CALCULATE (
[Growth],FILTER ('Account_Status_Table_Platform',[Account status platform] = "Lost")),
"Inactive",
CALCULATE (
[Growth],FILTER ('Account_Status_Table_Platform',[Account status platform] = "Inactive")))
  • AlexisOlson's avatar
    AlexisOlson
    Icon for Super User rankSuper User
    4 years ago

    This seems much simpler than the switch.

    Account status growth =
VAR CurrStatus =
    SELECTEDVALUE ( 'Account status'[Account status] )
RETURN
    CALCULATE (
        [Growth],
        FILTER ( 'Account_Status_Table_Segment', [Account status segment] = CurrStatus )
    )