I can't follow Tableau's examples on level of detail and I'm hoping someone can help.
I have row-level data that shows daily revenue. But I need to share that revenue with another party according to a fixed scale that's dependent on the total revenue earned in the calendar month:
ie. x% if Total monthly revenue < $A and y% of Total monthly revenue >= $A.
How can I apply the correct % at row level on each daily record, so that days contained in a month totalling less than $A have x% applied to them, and days contained in a month totalling more than $A have y% applied to them?
Example: Below is a table of sample data.
The row-level raw data contains date and revenue.
The "share" column shows the resulting values I am trying to achieve,
if $A = $3500, x=50, y = 60.
The total for November is less than $3500 and so each row in November gets 50%, and the total for December is more than $3500 therefore each row in December gets 60%.
| date | revenue | share |
|---|---|---|
| 1/11/2022 | 105.00 | 50% |
| 2/11/2022 | 110.00 | 50% |
| 3/11/2022 | 106.00 | 50% |
| 4/11/2022 | 89.00 | 50% |
| 5/11/2022 | 119.00 | 50% |
| 6/11/2022 | 121.00 | 50% |
| 7/11/2022 | 106.00 | 50% |
| 8/11/2022 | 127.00 | 50% |
| 9/11/2022 | 120.00 | 50% |
| 10/11/2022 | 102.00 | 50% |
| 11/11/2022 | 118.00 | 50% |
| 12/11/2022 | 101.00 | 50% |
| 13/11/2022 | 134.00 | 50% |
| 14/11/2022 | 118.00 | 50% |
| 15/11/2022 | 92.00 | 50% |
| 16/11/2022 | 121.00 | 50% |
| 17/11/2022 | 133.00 | 50% |
| 18/11/2022 | 113.00 | 50% |
| 19/11/2022 | 111.00 | 50% |
| 20/11/2022 | 104.00 | 50% |
| 21/11/2022 | 127.00 | 50% |
| 22/11/2022 | 115.00 | 50% |
| 23/11/2022 | 124.00 | 50% |
| 24/11/2022 | 88.00 | 50% |
| 25/11/2022 | 124.00 | 50% |
| 26/11/2022 | 135.00 | 50% |
| 27/11/2022 | 115.00 | 50% |
| 28/11/2022 | 111.00 | 50% |
| 29/11/2022 | 111.00 | 50% |
| 30/11/2022 | 112.00 | 50% |
| 1/12/2022 | 125.00 | 60% |
| 2/12/2022 | 134.00 | 60% |
| 3/12/2022 | 123.00 | 60% |
| 4/12/2022 | 123.00 | 60% |
| 5/12/2022 | 115.00 | 60% |
| 6/12/2022 | 100.00 | 60% |
| 7/12/2022 | 112.00 | 60% |
| 8/12/2022 | 121.00 | 60% |
| 9/12/2022 | 119.00 | 60% |
| 10/12/2022 | 101.00 | 60% |
| 11/12/2022 | 121.00 | 60% |
| 12/12/2022 | 123.00 | 60% |
| 13/12/2022 | 131.00 | 60% |
| 14/12/2022 | 122.00 | 60% |
| 15/12/2022 | 111.00 | 60% |
| 16/12/2022 | 115.00 | 60% |
| 17/12/2022 | 111.00 | 60% |
| 18/12/2022 | 121.00 | 60% |
| 19/12/2022 | 112.00 | 60% |
| 20/12/2022 | 125.00 | 60% |
| 21/12/2022 | 103.00 | 60% |
| 22/12/2022 | 135.00 | 60% |
| 23/12/2022 | 127.00 | 60% |
| 24/12/2022 | 119.00 | 60% |
| 25/12/2022 | 135.00 | 60% |
| 26/12/2022 | 110.00 | 60% |
| 27/12/2022 | 117.00 | 60% |
| 28/12/2022 | 128.00 | 60% |
| 29/12/2022 | 133.00 | 60% |
| 30/12/2022 | 103.00 | 60% |
| 31/12/2022 | 113.00 | 60% |
Given your sample data, make a calc field for Share:
This is an LOD fixed on truncing the date to month and summing their revenue.