Check if Salespeople Split Accounts in Given Month

[Anonymous]

Hi all, 

I have a problem where I'm trying to replicate the COUNTIFS function of Excel in PowerBI. Basically, I have sales data that gives me the Month of sales, Account Name and Salesperson. For calculating commission, the Salesperson gets full commission if only they sold to that customer in that month. If two salespersons sell to the same customer in the same month, the pot is split 50/50. If Person A sells to Customer A in Month 1 and Person B sells to Customer A in Month 2, no commissions are split. 

This is very easy to do in Excel but I'm finding it tricky in PowerBI - see attached Raw Data and Expected Outcome. I don't think I can use CALCULATE(FILTER(...)) as I need the values to appear in the column so that I can then filter for all the '0.5s'. 

Any advice on how this could be achieved in BI? Your help is greatly appreciated! OutcomeData

[Icey · Accepted solution]

Hi @Anonymous ,

@Greg_Deckler’s formula is great, but there are two typos. I have marked it in the screenshot below. After modifying it, the result is the same as you want.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Icey · Accepted solution]

Hi @Anonymous ,

@Greg_Deckler’s formula is great, but there are two typos. I have marked it in the screenshot below. After modifying it, the result is the same as you want.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@Icey @Greg_Deckler 

Thanks again for your help with this. I wondered if you could help me with this slight modification. 

How would I change the formula if I wanted to only count the account once and there were multiple entries in a given month? See attached image - in this, John sold to A twice in the same month, so I only want the first entry to register as a 1 for that month. When he sells to them again in Month 2, he gets a 1 as he only sold to that company once. 

I've tried adding in helper columns and calculating from that but it isn't working. I'm sure it must be a slight tweak to your formulas but I can't find that tweak at the minute. 

Thank you again!! 

[Icey · Accepted solution]

Hi @Anonymous ,

To my understand, the first value should be 1, right?

If so, try this:

1. Add an index column in Power Query Editor.

2. Create a column like so:

Column =
VAR __Account = [Account]
VAR __Month =
    MONTH ( [Month] )
VAR __Year =
    YEAR ( [Month] )
VAR __Index = [Index]
VAR __Salesperson = [Salesperson]
VAR __Table =
    ADDCOLUMNS (
        'Table (2)',
        "__Year", YEAR ( [Month] ),
        "__Month", MONTH ( [Month] )
    )
VAR __Table1 =
    SUMMARIZE ( __Table, [__Year], [__Month], [Account], [Salesperson], [Index] )
VAR result1 =
    COUNTROWS (
        DISTINCT (
            SELECTCOLUMNS (
                FILTER (
                    __Table1,
                    [__Month] = __Month
                        && [__Year] = __Year
                        && [Account] = __Account
                ),
                "__Salesperson", [Salesperson]
            )
        )
    )
VAR result2 =
    COUNTROWS (
        FILTER (
            __Table1,
            [__Month] = __Month
                && [__Year] = __Year
                && [Account] = __Account
                && [Salesperson] = __Salesperson
                && [Index] <= __Index
        )
    )
RETURN
    IF ( result2 > 1, 0, IF ( result1 = 1, 1, 0.5 ) )

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@Icey  This is amazing - you're a lifesaver, thank you! 

[Anonymous]

This is fantastic, thank you both!! 

[Greg_Deckler]

@Anonymous Perhaps try this:

Included? Column =
  VAR __Account = [Account])
  VAR __Month = MONTH([Month])
  VAR __Year = YEAR([Month])
  VAR __Table = ADDCOLUMNS('Table',"__Year",YEAR([Month]),"__Month",MONTH([Month]))
  VAR __Table1 = SUMMARIZE(__Table,[Year],[Month],[Account])
RETURN
  IF(COUNTROWS(FILTER(__Table1,[__Month]=__Month && [__Year]=__Year && [Account]=__Account))=1,1,.5)

[Anonymous]

@Greg_Deckler  - Thank you for this! However, it doesn't seem to consider the Salesperson? When I run this formula on my dataset, it seems to count how many times the name appears, but doesn't consider whether 2 persons have sold to 1 company in 1 month? 

Apologies if I've missed something, I'm very new to this. 

[Greg_Deckler]

@Anonymous Nope, I missed that, should be as below. Also updated this so if the same person sells twice in the same month to the same account that it returns 1 and not .5.

Included? Column =
  VAR __Account = [Account])
  VAR __Month = MONTH([Month])
  VAR __Year = YEAR([Month])
  VAR __Table = ADDCOLUMNS('Table',"__Year",YEAR([Month]),"__Month",MONTH([Month]))
  VAR __Table1 = SUMMARIZE(__Table,[Year],[Month],[Account],[Salesperson])
RETURN
  IF(COUNTROWS(DISTINCT(SELECTCOLUMNS(FILTER(__Table1,[__Month]=__Month && [__Year]=__Year && [Account]=__Account),"__Salesperson",[Salesperson])))=1,1,.5)