Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
apohl1
Helper I
Helper I

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 "Gained", "Gaining", "Losing", "Lost", "Stable".

 

I've managed to set up a formula that counts number of accounts classified to each account status segment, but I'm not able to write a similar formula to sum up the sales amount from the customers of each account status. Does anyone know if INTERSECT formula can do that, meaning not only count number of matching results but also can sum up the value of the matching results?  

 

SUMX (
INTERSECT (
SELECTCOLUMNS (
SUMMARIZE (
Account_Status_Table_Platform,
Account_Status_Table_Platform[Country+PAC+Platform],
"Type", CONVERT([Account status platform],STRING)
),
"Type", [Type]
),
VALUES ( 'Account status'[Account status] )
),
1
)

 

Thank you in advance!

2 ACCEPTED SOLUTIONS

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

View solution in original post

Variables will help with these measures too.

Account status platform =
VAR Threshold = SELECTEDVALUE ( 'Selection: account status threshold'[Threshold] )
VAR CurrPeriod = [Current Period]
VAR PrevPeriod = [Previous Period]
VAR GrowthPct = [Growth %]
VAR CurrPeriodIsBlank = ( ISBLANK ( CurrPeriod ) || ROUND ( CurrPeriod, 0 ) = 0 )
VAR PrevPeriodIsBlank = ( ISBLANK ( PrevPeriod ) || ROUND ( PrevPeriod, 0 ) = 0 )
RETURN
    SWITCH (
        TRUE (),
        CurrPeriodIsBlank && PrevPeriodIsBlank, "Inactive",
        CurrPeriodIsBlank && ROUND ( PrevPeriod, 0 ) > 0, "Lost",
        GrowthPct <= - Threshold, "Lost",
        PrevPeriodIsBlank && ROUND ( CurrPeriod, 0 ) > 0, "Gained",
        GrowthPct >= Threshold, "Gained",
        GrowthPct >= 0.05, "Gaining",
        PrevPeriod <> 0 && GrowthPct <= -0.05, "Losing",
        "Stable"
    )

 

That should help some but what would really help is if you could keep this from computing for every single row of the table you're filtering, so the critical question is how is this measure related to the row context of 'Account_Status_Table_Platform'? Is there anything that changes from row to row that changes the output of this table? How are [Current Period] and [Previous Period] defined? Do these depend on 'Account_Status_Table_Platform' at all?

View solution in original post

13 REPLIES 13
apohl1
Helper I
Helper I

Thanks so much @AlexisOlson!! I had no idea using variables could improve the performance so much (a visual that used to take 45 seconds to run now takes 8 seconds!). It solves my problem for sure!

 

One additional question if I may.. I would like to add two flexible dynamics in the formula (so that account status is based on what the user selects as Account level and Product level - ultimately different aggregation of accounts and group of products), do you see anything to optimaize more in the formula below? The fprmula performance with these two additional flexibilities is not bad, I just thought in case you have any ideas to improve it further 🙂

 

Thanks so much!! 

 

Account status growth (with account and product lvl) =
VAR CurrStatus =
SELECTEDVALUE ( 'Account status'[Account status] )
VAR CurrAccountlvl =
SELECTEDVALUE ( 'Account level'[Account level] )
VAR CurrProductlvl =
SELECTEDVALUE ( 'Selection: product level'[Product level] )

RETURN
SWITCH(CurrProductlvl,"Platform",
SWITCH(CurrAccountlvl,"CAC",
CALCULATE (
[Growth],
FILTER ( 'Account_Status_Table_Platform', [Account status Platform] = CurrStatus )),
"PAC",
CALCULATE (
[Growth],
FILTER ( 'Paccount_Status_Table_Platform', [Parent Account status platform] = CurrStatus ))),
"Product",
SWITCH(CurrAccountlvl,"CAC",
CALCULATE (
[Growth],
FILTER ( 'Account_Status_Table_Product', [Account status product] = CurrStatus )),
"PAC",
CALCULATE (
[Growth],
FILTER ( 'Paccount_Status_Table_Product', [Parent Account status product] = CurrStatus ))),
"Segment",
SWITCH(CurrAccountlvl,"CAC",
CALCULATE (
[Growth],
FILTER ( 'Account_Status_Table_Segment', [Account status segment] = CurrStatus )),
"PAC",
CALCULATE (
[Growth],
FILTER ( 'Paccount_Status_Table_Segment', [Parent Account status segment] = CurrStatus ))))

I can't think of something much better. You could move stuff around to make it look different but I don't see a way to avoid the general structure.

 

You'll likely get better performance filtering on single columns rather than entire tables though. See if this is any faster:

Account status growth (with account and product lvl) =
VAR CurrStatus     = SELECTEDVALUE ( 'Account status'[Account status] )
VAR CurrAccountlvl = SELECTEDVALUE ( 'Account level'[Account level] )
VAR CurrProductlvl = SELECTEDVALUE ( 'Selection: product level'[Product level] )
VAR PlatformCAC =
    TREATAS ( { CurrStatus }, 'Account_Status_Table_Platform'[Account status Platform] )
VAR PlatformPAC =
    TREATAS ( { CurrStatus }, 'Paccount_Status_Table_Platform'[Parent Account status platform] )
VAR ProductCAC =
    TREATAS ( { CurrStatus }, 'Account_Status_Table_Product'[Account status product] )
VAR ProductPAC =
    TREATAS ( { CurrStatus }, 'Paccount_Status_Table_Product'[Parent Account status product] )
VAR SegmentCAC =
    TREATAS ( { CurrStatus }, 'Account_Status_Table_Segment'[Account status segment] )
VAR SegmentPAC =
    TREATAS ( { CurrStatus }, 'Paccount_Status_Table_Segment'[Parent Account status segment] )
RETURN
    SWITCH (
        CurrProductlvl,
        "Platform",
            SWITCH (
                CurrAccountlvl,
                "CAC", CALCULATE ( [Growth], PlatformCAC ),
                "PAC", CALCULATE ( [Growth], PlatformPAC )
            ),
        "Product",
            SWITCH (
                CurrAccountlvl,
                "CAC", CALCULATE ( [Growth], ProductCAC ),
                "PAC", CALCULATE ( [Growth], ProductPAC )
            ),
        "Segment",
            SWITCH (
                CurrAccountlvl,
                "CAC", CALCULATE ( [Growth], SegmentCAC ),
                "PAC", CALCULATE ( [Growth], SegmentPAC )
            )
    )

I wonder if you are able to help me on another formula? In the formula below I want to sum up sales growth from "Gained" and "Lost" accounts but the performance is really bad (I think it's because I use FILTER function).. Thanks a million!! 🙂 

 

Growth through Net account conversion =
VAR CurrAccountlvl =
SELECTEDVALUE ( 'Account level'[Account level] )
VAR CurrProductlvl =
SELECTEDVALUE ( 'Selection: product level'[Product level] )

RETURN
SWITCH(CurrProductlvl,"Platform",
SWITCH(CurrAccountlvl,"CAC",
CALCULATE (
[Growth],
FILTER ( 'Account_Status_Table_Platform', [Account status Platform] = "Gained" ))+
CALCULATE (
[Growth],
FILTER ( 'Account_Status_Table_Platform', [Account status Platform] = "Lost" )),
"PAC",
CALCULATE (
[Growth],
FILTER ( 'Paccount_Status_Table_Platform', [Parent Account status platform] = "Gained" ))+
CALCULATE (
[Growth],
FILTER ( 'Paccount_Status_Table_Platform', [Parent Account status platform] = "Lost" ))
),
"Product",
SWITCH(CurrAccountlvl,"CAC",
CALCULATE (
[Growth],
FILTER ( 'Account_Status_Table_Product', [Account status product] = "Gained" ))+
CALCULATE (
[Growth],
FILTER ( 'Account_Status_Table_Product', [Account status product] = "Lost" )),
"PAC",
CALCULATE (
[Growth],
FILTER ( 'Paccount_Status_Table_Product', [Parent Account status product] = "Gained" ))+
CALCULATE (
[Growth],
FILTER ( 'Paccount_Status_Table_Product', [Parent Account status product] = "Lost" ))
),
"Segment",
SWITCH(CurrAccountlvl,"CAC",
CALCULATE (
[Growth],
FILTER ( 'Account_Status_Table_Segment', [Account status segment] = "Gained" ))+
CALCULATE (
[Growth],
FILTER ( 'Account_Status_Table_Segment', [Account status segment] = "Lost" )),
"PAC",
CALCULATE (
[Growth],
FILTER ( 'Paccount_Status_Table_Segment', [Parent Account status segment] = "Gained" ))+
CALCULATE (
[Growth],
FILTER ( 'Paccount_Status_Table_Segment', [Parent Account status segment] = "Lost" ))
))

Using measures inside of a FILTER function on an entire table is computationally expensive since it means evaluating the measure for every single row of that table. Depending on how these measures are defined, there might be much more efficient ways to do this. What do these measures look like?

 

You should at least be able to combine the Gained and Lost pieces like this:

CALCULATE ( [Growth], FILTER ( 'Table1', [Measure1] IN { "Gained", "Lost" } ) )

Thanks for your help, with your suggestion the performance of one visual went from 55 sec to 37 sec so at least improving 🙂 unfortunately still not very fast. Intially I had hoped I could somehow leverage Intersect formula instead of Calculate with a filter, but so far I only made Interesct work when it's about counting the number of rows (number of accounts classified as "Gaining" etc), not summing up the total of a specific category (sales growth from accounts classified as "Gaining") which is what I need. 

 

With regards to the account status formula, it looks like below. If you have any ideas if this can be improved, please let me know 🙂

 

Account status platform =
VAR Threshold = SELECTEDVALUE ('Selection: account status threshold'[Threshold])

RETURN

SWITCH(TRUE(),

(ISBLANK([Current Period])|| round([Current Period],0)=0)

&&

(ISBLANK([Previous Period])|| round([Previous Period],0)=0),"Inactive",

(ISBLANK([Current Period])|| round([Current Period],0)=0)&&

round([Previous Period],0)>0,"Lost",

[Growth %] <= -Threshold,"Lost",

(ISBLANK([Previous Period])|| round([Previous Period],0)=0) &&

round([Current Period],0)>0,"Gained",

[Growth %]>=Threshold,"Gained",

[Growth %] >=0.05,"Gaining",

[Previous Period]<>0 && [Growth %]<=-0.05,"Losing",

"Stable")

Variables will help with these measures too.

Account status platform =
VAR Threshold = SELECTEDVALUE ( 'Selection: account status threshold'[Threshold] )
VAR CurrPeriod = [Current Period]
VAR PrevPeriod = [Previous Period]
VAR GrowthPct = [Growth %]
VAR CurrPeriodIsBlank = ( ISBLANK ( CurrPeriod ) || ROUND ( CurrPeriod, 0 ) = 0 )
VAR PrevPeriodIsBlank = ( ISBLANK ( PrevPeriod ) || ROUND ( PrevPeriod, 0 ) = 0 )
RETURN
    SWITCH (
        TRUE (),
        CurrPeriodIsBlank && PrevPeriodIsBlank, "Inactive",
        CurrPeriodIsBlank && ROUND ( PrevPeriod, 0 ) > 0, "Lost",
        GrowthPct <= - Threshold, "Lost",
        PrevPeriodIsBlank && ROUND ( CurrPeriod, 0 ) > 0, "Gained",
        GrowthPct >= Threshold, "Gained",
        GrowthPct >= 0.05, "Gaining",
        PrevPeriod <> 0 && GrowthPct <= -0.05, "Losing",
        "Stable"
    )

 

That should help some but what would really help is if you could keep this from computing for every single row of the table you're filtering, so the critical question is how is this measure related to the row context of 'Account_Status_Table_Platform'? Is there anything that changes from row to row that changes the output of this table? How are [Current Period] and [Previous Period] defined? Do these depend on 'Account_Status_Table_Platform' at all?

I now implemented your improved formula for account status and it seems it improves the perfromance of Calulcate with filter A LOT! It is no longer an issue to filter through the Account_Status_Table_Platform, the performance went from 34 sec to 3 with just fixing the account status. Thanks so much for all your help on this! 🙂

Variables for the win!

Thank you!! I will use your formula to improve the account status.

Regarding avioding having to compute every single row, I don't know how to do it. The Account_Status_Table_Platform is a table with a relationship (single relationship) to my sales table (my raw data table with detailed sales by account and product level by month). I use the Account_Status_Table_Platform to aggregate the sales on the level I need to do the account status measure on (in this case I aggregate on country/CAC/platform(group of product). The Account status table is generated by summarizecolumn and is therefore a static column that doesn't change depending on the user selection for time range (unfortunately therefore it has many rows I believe). Do you have any idea how I could limit number of rows in this table and still ensure it captures all cases (combination of countr/customer account code/platform) depending on user point in time and time comparison selection?

 

Account_Status_Table_Platform = SUMMARIZECOLUMNS('Sales'[Country+CAC+Platform])
 
Current and previous period measures doesn't change the output of the Account_Status_Table_Platform table - rather the other was around, they are aggregated on the level defined in Account_Status_Table_Platform table to ensure the account classification is based on the right aggregation.
Current Period =
VAR MySelection = SELECTEDVALUE('Selection: comparison period'[ID], 1)
RETURN
SWITCH(
TRUE(),
MySelection = 1, TOTALYTD([Measure], 'Calendar'[D. Date]),
MySelection = 2, TOTALYTD([Measure], 'Calendar'[D. Date]),
MySelection = 3,TOTALQTD([Measure], 'Calendar'[D. Date]),
MySelection = 4, TOTALQTD([Measure], 'Calendar'[D. Date]),
MySelection = 5, CALCULATE([Measure], DATESINPERIOD('Calendar'[D. Date],Max('Calendar'[D. Date]),-12,MONTH)),
MySelection = 6, CALCULATE([Measure], DATESINPERIOD('Calendar'[D. Date],Max('Calendar'[D. Date]),-6,MONTH)),
MySelection = 7, CALCULATE([Measure], DATESINPERIOD('Calendar'[D. Date],Max('Calendar'[D. Date]),-3,MONTH)))
 
Previous Period =
VAR MySelection = SELECTEDVALUE('Selection: comparison period'[ID], 1)
RETURN
SWITCH(
TRUE(),
MySelection = 1, CALCULATE(TOTALYTD([Measure], 'Calendar'[D. Date]),SAMEPERIODLASTYEAR('Calendar'[D. Date])),
MySelection = 2, CALCULATE(TOTALYTD([Measure], 'Calendar'[D. Date]), DATEADD('Calendar'[D. Date] ,-2,YEAR)),
MySelection = 3, CALCULATE(TOTALQTD([Measure], 'Calendar'[D. Date]),SAMEPERIODLASTYEAR('Calendar'[D. Date])),
MySelection = 4, CALCULATE(TOTALQTD([Measure], 'Calendar'[D. Date]), DATEADD('Calendar'[D. Date] ,-2,YEAR)),
MySelection = 5, CALCULATE(CALCULATE([Measure], DATESINPERIOD('Calendar'[D. Date],Max('Calendar'[D. Date]),-12,MONTH)), SAMEPERIODLASTYEAR('Calendar'[D. Date])),
MySelection = 6, CALCULATE(CALCULATE([Measure], DATESINPERIOD('Calendar'[D. Date],Max('Calendar'[D. Date]),-6,MONTH)), SAMEPERIODLASTYEAR('Calendar'[D. Date])),
MySelection = 7, CALCULATE(CALCULATE([Measure], DATESINPERIOD('Calendar'[D. Date],Max('Calendar'[D. Date]),-3,MONTH)), SAMEPERIODLASTYEAR('Calendar'[D. Date])))

OK. I think this has gone beyond what I can digest without actually looking at the model.

 

I suspect that some modifications could make a big difference, but I can't see enough to say exactly what they would be.

Thank you for the advice! Unfortunately the suggested formula doens't work because all account status forumlas such as "Account_Status_Table_Platform'[Account status Platform]" are measures. They are measures and not columns because they should change based on user selection of point in time and time comparison. 

apohl1
Helper I
Helper I

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")))

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

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.

Top Solution Authors