Count Values multiplied by a measure

[rachk]

Hi

I have tried mutiple ways of making this work. I have fields that are dates, so month and day they departed, month and day they arrived which is then calculated into number of days. I have then calculated how many people (Count of ID) on this date travelled. What I am attempting (and failing to do) is calculate the number of days by the Count of ID to end up with a total number of days travelled. The below is the snap of what is currently there. 

The last one I tried:

TotalImpact=

{COUNTROWS (ourformname)* Max (ourformname [Number of Days]}

Would LOVE any guidance on this one. 

[Anonymous]

How about something like:
VAR CountID = count([ID)
[Number of days(measure)] * CountID

Why wouldnt this work? 

Or if the count id is already a measure just do number_of_days * count_id

[pankajnamekar25]

Hello @rachk 

Try this Measure

GroupedTravel =

SUMMARIZE(

    TravelData,

    TravelData[DepartureDate],

    TravelData[ReturnDate],

    "Number of People", COUNT(TravelData[ID]),

    "Days per Person", MAX(TravelData[Number of Days]),

    "Total Days Travelled", SUM(TravelData[Number of Days])

)

Thanks,
 Pankaj Namekar | LinkedIn

If this solution helps, please accept it and give a kudos (Like), it would be greatly appreciated.

[rachk]

This is what I have entered using our sheet name and think Ive done something wrong here! 

[Ashish_Excel]

Hi,

Share some data to work with and show the expected result.  Share data in a format that can be pasted in an MS Excel file.

[rachk]

DepartureDate	ReturnDate	Number of Days	ID	Desired Outcome (Total Days Travelled)
16/03/2025 5:00:00 PM	20/03/2025 8:00:00 PM	4	1	4
8/04/2025 7:00:00 AM	8/04/2025 3:00:00 PM	1	2	2
14/04/2025 6:00:00 AM	14/04/2025 4:00:00 PM	1	2	2
1/05/2025 6:30:00 AM	2/05/2025 10:00:00 AM	1	1	1
14/05/2025 12:00:00 AM	14/05/2025 12:00:00 AM	1	2	2
15/05/2025 10:00:00 AM	17/05/2025 2:00:00 PM	2	2	4
18/05/2025 2:19:00 PM	24/05/2025 7:13:00 AM	5	1	5
22/05/2025 8:00:00 AM	26/05/2025 6:00:00 PM	4	1	4
25/05/2025 4:00:00 PM	28/05/2025 8:00:00 PM	3	2	5
27/05/2025 6:30:00 AM	27/05/2025 7:40:00 PM	1	2	2
27/05/2025 7:00:00 AM	28/05/2025 8:00:00 PM	1	1	1
28/05/2025 12:00:00 PM	30/05/2025 1:00:00 PM	2	1	2
4/06/2025 8:30:00 AM	6/06/2025 5:00:00 PM	2	1	2
5/06/2025 1:30:00 AM	6/06/2025 9:00:00 AM	1	1	1

[Ashish_Excel]

I cannot understand the logic used for arriving at the numbers mentioned in the last column.  Give an explanation.

[rachk]

There are multiple people travelling on the same day, the ID is a count of those people. Number of Days times by ID makes up for the total days travelled. 

The excel sheet didnt come across very well differentiated so ID is squashed up with Numbers of Days as the heading.

[Ashish_Excel]

Share the download link of the Excel file with your Excel formulas written there.  I will convert those formulas into DAX measures.

[rachk]

There is no formula here. I copied the columns out of our BI table which comes via PowerApps. I looked at the dates that had several people travelling on the same date and put that figure in the last column. 

Eg 25/5/25 - 28/5/25 is 3 days x 2 people should be 6 in the last column (I added so got that wrong above) 

Another example is we had people travel on 6th June for 1 day, there were 13 of them so desired outcome would be 13 in that column. 

What is currently happening is we get line by line without a combination of those dates. 

[Ashish_Excel]

In the second row, why is the desired oucome 2?

[rachk]

Because there are 2 ID's travelling on that date (the column that has ID is 2 - 2x1=2)

[Irwan]

hello @rachk 

i might be misunderstood but i assume you want to multiply two columns in measure.

Based on above discussion, i get a slight idea that all column in your sample data are column (none of them are measure).

then try this DAX, lets see if this accomodate your requirement.

Desired Outcome =
var _Number = 
MAXX(
    'Table',
    'Table'[Number of Days]
)
var _Count = 
MAXX(
    'Table',
    'Table'[ID]
)
Return
_Count*_Number

as far as my knowledge, MAXX/MINX/COUNTX/SUMX works at row level expression while MAX/MIN/COUNT/SUM works at column level.

since you want to get a value each row so your value might be a little bit off if using MAX or COUNT unless you have another condition or DAX to make MAX or COUNT works extracting row value.

Hope this will help.

Thank you.

[rachk]

Hi

Number of days are a measure - it is calculating the days between Departure Date and Return Date to get the Number of Days Column. 

Then the Count of ID is showing the amount of staff travelling on that day. 

Your measure isnt working for me unfortunately

[Irwan]

hello @rachk 

your syntax is incorrect.

line 1 should be measure name.

Thank you.

[rachk]

That worked, but didnt return the right values

[Irwan]

hello @rachk 

i thought there is no measure since you said there is no formula.

if number of days and count of id are measures, then you can multiply it directly.

here i made number of days and count of id as a measure.

Number of days is a measure, but i am not sure about the count of id.

if the count of id is a column, then you need to create a measure for count of id

after that, multiply measure with measure.

Thank you.

[rachk]

I mentioned no formula for the desired column, thats true - that is what I am trying to obtain. 

What you have sent, makes perfect sense, however, is not working and I think it is because the information is pulling from several rows, not an excel file like you have which is a standard date - mine is column of month and day and then I can get the measure of days. The ID is from power apps which is individual people who have applied for travel and it equates to 1 per person, if there are more than 1 on a date, it will then "count" for that date. 

I havent found anything that will multiply the number of days and ID in that way

[Irwan]

Hello @rachk 

From your description, the previous sample you gave seems not represent the issue you are facing.

Please share a sample data that represent your problem. It will be great if you can give your pbix to work on (remove all confidential information)

Thank you.

[v-menakakota]

Hi @rachk ,

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot). 

Do not include sensitive information. Do not include anything that is unrelated to the issue or question. 

Please show the expected outcome based on the sample data you provided. 

Best Regards, 
Menaka Kota 
Community Support Team

[v-menakakota]

Hi  @rachk ,

As we haven’t heard back from you, so just following up to our previous message. I'd like to confirm if you've successfully resolved this issue or if you need further help. 

If yes, you are welcome to share your workaround and mark it as a solution so that other users can benefit as well. If you find a reply particularly helpful to you, you can also mark it as a solution. 
If you still have any questions or need more support, please feel free to let us know. We are more than happy to continue to help you. 
Thank you for your patience and look forward to hearing from you.