Forum Discussion

CiuCiCiao's avatar
CiuCiCiao
Icon for Helper I rankHelper I
9 years ago
Solved

Assistance needed with formula

Hi Guys, As in the table below I have Customer ID for each Service and ha date. I concatenate Customer ID and Date to have a unique Key of Entry / Admission. I need to spread Front Office cost by ...
Need Help
  • TheOckieMofo's avatar
    TheOckieMofo
    9 years ago

    You need to get familiar with the early function. It's one of those functions that I'm not totally sure why it works, I just know it does. So let's learn by doing.

     

    So you can use this function to create a count of the rows that have each unique key. The syntax would be:

     

    =CALCULATE(COUNTA([KEY]),FILTER('TABLENAME','TABLENAME'[KEY]=EARLIER('TABLENAME'[KEY])))

     

    This would be the denominator in your equation, so you could just put a "1/" in front of the above equation to get your proper value.

     

    That should work for you. Let me know.

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Get the overall count by key. So for Customer 1, this number would be 4:

 

OverallCountByKey:=CALCULATE(count(Sheet1[Key]),ALLEXCEPT(Sheet1,Sheet1[Key]))

 

Then divide the count of key by this new measure:

 

% of Total:=DIVIDE(Count(Sheet1[Key]),[OverallCountByKey])

 

 

 

  • CiuCiCiao's avatar
    CiuCiCiao
    Icon for Helper I rankHelper I
    9 years ago

    AnonymousI went through something that looks worst :D

     

    =
1
    / CALCULATE (
        COUNTA ( [KEY] ),
        FILTER ( 'TABLENAME', 'TABLENAME'[KEY] = EARLIER ( 'TABLENAME'[KEY] ) )
    )
    / SUMX (
        'TABLENAME',
        1
            / CALCULATE (
                COUNTA ( [KEY] ),
                FILTER ( 'TABLENAME', 'TABLENAME'[KEY] = EARLIER ( 'TABLENAME'[KEY] ) )
            )
    )

    This works for me though! I'll try your formula Monday since it looks cleaner!

    Thanks