Calculate total unique Daily margins in a month

[Anonymous]

This forum is one of th best I've ever used! This one I just can't get my head around. I've managed to calculate a cumulative number of runners monthly 

Cumulative Runners =
CALCULATE (
COUNT( Bookings[Cons 1 ] ),
FILTER (
ALL ( DateTable[Date] ),
DateTable[Date] <= MAX ( DateTable[Date] )
)
)

I can total the margins but this counts everyday in the month e.g a daily margin in a contract of 100 in Jan should = 100 for Bob in that month not 100 x no of days in the month. So if a Sales person sells 5 contracts in Jan each at 100 it should = 500 not 5 x 100 x no of days in each month.

I hope this makes sense. 

[v-shex-msft]

Hi @Anonymous,

I'd like to suggest you add a condition to limit the filter to same month, then use distinctcount to calculate the contract count.

Cumulative Runners =
CALCULATE (
DISTINCTCOUNT( Bookings[Cons 1 ] ),
FILTER (
ALL ( DateTable[Date] ),
DateTable[Date] <= MAX ( DateTable[Date] )&&Format(DateTable[Date],"mm/yyyy") = Format(MAX(DateTable[Date]),"mm/yyyy")
)
)

Regards,

Xiaoxin Sheng

[Anonymous]

Thanks but this will produce a count of number of contracts in a period . I need to SUM the contract daily margin amount (e.g 150 per contract I have an contract ref (1000), an associated user ref (1234) and a row per day of the month (1/1/2017, 2/1/2017 with the same data on other fields mentioned. The challenge is to only SUM the 150 once per month? Any ideas?   

[v-shex-msft]

Hi @Anonymous,

>>The challenge is to only SUM the 150 once per month? Any ideas?   

Perhaps you can try below formula(I haven't test on actual data yet).

Logic:
1. Use calculate table to filter table,
2. Use sumx function to summary distinct value.

Amount =
SUMX(
CALCULATETABLE(
Booking,
FILTER (
ALL( DateTable[Date] ),
DateTable[Date] <= MAX ( DateTable[Date] )&&Format(DateTable[Date],"mm/yyyy") = Format(MAX(DateTable[Date]),"mm/yyyy")
),
Distinct(Booking[margin amount]))

Regards,

Xiaoxin Sheng

[Anonymous]

Thanks but I get an error;

Too few arguments were passed to the SUMX function. The minimum argument count for the function is 2.

I looked this up and confirmed that it is because there is only one filter applied, any ideas?

[v-shex-msft]

Hi @Anonymous,

Sorry for that, I paste the code without double check(I loss the end ")" of calculatetable function), you can use the modified formula to test again:

Amount =
SUMX(
CALCULATETABLE(
Booking,
FILTER (
ALL( DateTable[Date] ),
DateTable[Date] <= MAX ( DateTable[Date] )&&Format(DateTable[Date],"mm/yyyy") = Format(MAX(DateTable[Date]),"mm/yyyy")
)),
Distinct(Booking[margin amount]))

Regards,
Xiaoxin Sheng

[Anonymous]

Thanks, when trying to show values returned I get error in visualisation

A table of multiple values was supplied where a single value was expected.

I checked and there is a row per day and a corresponding margin so only one row should be returned. Once this is resolved I will need to add a column for row numbers as there is potential for the same daily margin for a different contract running on the same day. 

[v-shex-msft]

Hi @Anonymous,

Can you please share some sample data to test?

Regards,
Xiaoxin Sheng

[Anonymous]

bookings.csv 

I couldn't find a way to attach a file directly. I presume a link is the way to do it? Are you able to access it? 

[v-shex-msft]

Hi @Anonymous,

Based on your sample, you want to calcualte the running margin cost,right ?

If as I said, I'd like to suggest you summary a "Daily Margin" table, then use it to calculate the distinct running total.

Table = SUMMARIZE(BookingsExtract,[start_date],[end_date],[event_ref] ,"Daily Margin",DISTINCT(BookingsExtract[Daily Margin]))

Then you can use date and id to find out the actual daily margin, then use daily margin to calculate the total margin(day count * current margin)

Notice: id means the "contract ID", I haven't found it in table, perhaps you can use it to summary table.

Regards,

Xiaoxin Sheng

[Anonymous]

I need to sum the daily margin number £157.50 in a period for an event_ref (contract id) against the Cons 1 associated and repeat for the Cons 2, then add the two together.

I could add a row number column and then get Cons 1 ref , row number, daily margin and max date in month that should give me a single row for each month associated to the Cons 1 ref and the daily margin, right? 

I just need to work out a formula for the above. Hope the above makes sense.

[v-shex-msft]

Hi @Anonymous,

Based on your description, I modify the summary table.

How did you choose the specify daily margins in these duplicate "cons 1"?

Regards,

Xiaoxin Sheng

[Anonymous]

I need to use the Contract days not the start and end date. As I need to know the daily margins applicable to every day, month, quarter etc.

I guess I would return the max date in a month from the values? 

[v-shex-msft]

Hi @Anonymous,

Based on your description, you want get a summary table about Contract date and total daily margin, right?

Steps:

1. Summary table with contract days, event_ref, distinct daily margins:

Table = SUMMARIZE(BookingsExtract,[Contract Days],[event_ref],"Daily Margins",DISTINCT(BookingsExtract[Daily Margin]))

2. Summary above table and remove event_ref:

Table 2 = SUMMARIZE('Table',[Contract Days],"Total Margins",SUM('Table'[Daily Margins]))

Result:

>>As I need to know the daily margins applicable to every day, month, quarter etc.

You can add calculated column on the summary table with specific date range filter to get the specific range margins.

For example:

Weekly Margins = CALCULATE(SUM([Total Margins]),WEEKNUM('Table 2'[Contract Days],1)=WEEKNUM(EARLIER('Table 2'[Contract Days]),1)) 

Monthly Margins = CALCULATE(SUM([Total Margins]),MONTH('Table 2'[Contract Days])=MONTH(EARLIER('Table 2'[Contract Days]))) 

Regards,

Xiaoxin Sheng

[Anonymous]

I need to include the user the daily margin is associated to and sum it only once for each week, month etc. for each event ref. 

At the moment it is summing the daily margin for everyday in the month, week. Just need it summed once within week, month etc.

Then visualise filtering by user. 

Another challenge is that this is a split % of this daily margin between two people and that user could be the Split 1 % or Split 2 %. That perhaps a brainteaser at a later stage.

Thanks, John