Forum Discussion
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 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
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.
7 Replies
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.
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.
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
I'm totally not following this. Perhaps paste in your source data and what you want the result to look like?
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
