cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Calculate total unique Daily margins in a month

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. 

 

BookingTable.pngBookingtable2.pngGraphAttemptingToCreate.png

14 REPLIES 14
Community Support
Community Support

Re: Calculate total unique Daily margins in a month

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

Community Support Team _ Xiaoxin Sheng
If this post helps, please consider Accept it as the solution to help the other members find it more quickly
Learning resources: Power BI
Anonymous
Not applicable

Re: Calculate total unique Daily margins in a month

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?   

Community Support
Community Support

Re: Calculate total unique Daily margins in a month

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

Community Support Team _ Xiaoxin Sheng
If this post helps, please consider Accept it as the solution to help the other members find it more quickly
Learning resources: Power BI
Anonymous
Not applicable

Re: Calculate total unique Daily margins in a month

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?

Community Support
Community Support

Re: Calculate total unique Daily margins in a month

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

Community Support Team _ Xiaoxin Sheng
If this post helps, please consider Accept it as the solution to help the other members find it more quickly
Learning resources: Power BI
Anonymous
Not applicable

Re: Calculate total unique Daily margins in a month

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. 

Community Support
Community Support

Re: Calculate total unique Daily margins in a month

Hi @Anonymous,

 

Can you please share some sample data to test?

 

Regards,
Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, please consider Accept it as the solution to help the other members find it more quickly
Learning resources: Power BI
Anonymous
Not applicable

Re: Calculate total unique Daily margins in a month

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? 

Community Support
Community Support

Re: Calculate total unique Daily margins in a month

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]))

Capture.PNG

 

 

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

Community Support Team _ Xiaoxin Sheng
If this post helps, please consider Accept it as the solution to help the other members find it more quickly
Learning resources: Power BI

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors