Assistance needed with formula

[CiuCiCiao]

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 entry instead of number of service, so what I need is a formula that gives me the output of the table below for column "Entry".

Customer	Date	Service	Key	Entry
1	28/07/2017	A	142944	0,25
1	28/07/2017	B	142944	0,25
1	28/07/2017	E	142944	0,25
1	28/07/2017	F	142944	0,25
2	28/07/2017	D	242944	0,333333
2	28/07/2017	B	242944	0,333333
2	28/07/2017	F	242944	0,333333
3	28/07/2017	F	342944	0,5
3	28/07/2017	A	342944	0,5
4	28/07/2017	C	442944	1
1	29/07/2017	B	142945	0,5
1	29/07/2017	D	142945	0,5
3	29/07/2017	A	342945	1
4	29/07/2017	F	442945	0,5
4	29/07/2017	E	442945	0,5

Any hint how can i manage this in DAX?

Thanks

[Anonymous]

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]

@AnonymousI went through something that looks worst 😄

=
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

[TheOckieMofo · Accepted solution]

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.

[CiuCiCiao]

Works good! Thanks!

About the earlier function, I am using it in the same model inside a filter, but still I can't get why...

Link to the discussion

[TheOckieMofo]

Yeah, the EARLIER function is just one of those things. I always liken it to Calculus. I don't know how derivates/integrals work, but I assume the French guys in the 1700s got it right, so I use it. The only thing we need to know is when to apply it.

Anytime you need to compare rows instead of compare columns, you're probably going to use the Earlier function at some point.

[Greg_Deckler]

I'm totally not following this. Perhaps paste in your source data and what you want the result to look like?

[CiuCiCiao]

@Greg_DecklerI am sorry for my inaccuracy.

For all the column "Key" I need a formula to divide 1 by the number of each duplicate.

E.g. for: 142944  =(1/4)

Key	Entry	Formula
142944	0,25	1/4
142944	0,25	1/4
142944	0,25	1/4
142944	0,25	1/4
242944	0,333333	1/3
242944	0,333333	1/3
242944	0,333333	1/3
342944	0,5	1/2
342944	0,5	1/2
442944	1	1/1

Hope now is clearer.

Thanks