cancel
Showing results for
Did you mean:
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.

14 REPLIES 14
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

## 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

## 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

## 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

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

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

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

Announcements

#### 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!

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

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

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

Top Solution Authors
Top Kudoed Authors