Forum Discussion

rachk's avatar
rachk
Icon for Helper I rankHelper I
1 year ago
Solved

Count Values multiplied by a measure

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 c...
Need Help
  • v-menakakota's avatar
    v-menakakota
    1 year ago

    Hi rachk ,

    Try to check with this:

    Total Days Travelled =
    SUMX (
    SUMMARIZE (
    TravelData,
    TravelData[DepartureDate],
    TravelData[ReturnDate],
    "PeopleCount", COUNTROWS(TravelData),
    "TripDays", MAX(TravelData[Number of Days]) 
    ),
    [PeopleCount] * [TripDays]
    )
    Also please go through the updated pbix file.
    If this post was helpful, please give us Kudos and consider marking Accept as solution to assist other members in finding it more easily.
    Best Regards, 
    Menaka. 
    Community Support Team  

v-menakakota's avatar
v-menakakota
Icon for Community Support rankCommunity Support
1 year ago

Hi   rachk ,

Once you try to figure out how to mask sensitive data, Please share the data with us we will try to look into the issue.
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.
How to provide sample data in the Power BI Forum - Microsoft Fabric Community

Best Regards, 
Menaka. 
Community Support Team.

rachk's avatar
rachk
Icon for Helper I rankHelper I
1 year ago

Hi

Departure date ends up displayed with month and day as does return date. 

ID is per person - this on my table shows as Count of ID

Number of days if derived between the dates of Departure date and return date. 

I am seeking the total number of days travelled per line eg on 5th June, there were 4 people who travelled 1 night so the last column I need to add should read 5. ID will come up on my screen as 5 as it identifies 5 people travelled on that date for 1 night each. 

 

DepartureDateReturnDateIDNumber of Days
25/05/2025 4:45:00 PM28/05/2025 7:40:00 PM83
14/04/2025 6:00:00 AM14/04/2025 4:00:00 PM91
14/04/2025 6:30:00 AM14/04/2025 5:00:00 PM101
8/04/2025 7:00:00 AM8/04/2025 3:00:00 PM111
8/04/2025 7:00:00 AM8/04/2025 2:30:00 PM121
20/06/2025 1:00:00 PM21/06/2025 11:00:00 AM131
16/03/2025 5:00:00 PM20/03/2025 8:00:00 PM144
25/05/2025 4:00:00 PM28/05/2025 8:00:00 PM153
1/05/2025 6:30:00 AM2/05/2025 10:00:00 AM181
28/05/2025 12:00:00 PM30/05/2025 1:00:00 PM212
20/06/2025 6:30:00 AM20/06/2025 7:30:00 PM221
15/05/2025 10:00:00 AM17/05/2025 2:00:00 PM232
5/06/2025 1:25:00 PM6/06/2025 7:00:00 AM241
5/06/2025 11:00:00 AM9/06/2025 2:00:00 PM254
5/06/2025 8:00:00 AM9/06/2025 12:00:00 AM263
5/06/2025 9:00:00 AM6/06/2025 5:00:00 PM281
5/06/2025 10:20:00 AM6/06/2025 4:20:00 PM291
5/06/2025 1:25:00 PM6/06/2025 1:00:00 PM301
14/05/2025 5:45:00 AM14/05/2025 8:30:00 PM311
14/05/2025 12:00:00 AM14/05/2025 12:00:00 AM321
5/06/2025 10:20:00 AM6/06/2025 11:55:00 AM331
18/05/2025 2:19:00 PM24/05/2025 7:13:00 AM345
15/05/2025 10:00:00 AM17/05/2025 2:00:00 PM352
27/05/2025 7:00:00 AM28/05/2025 8:00:00 PM361
19/06/2025 1:25:00 PM20/06/2025 7:40:00 PM371
  • v-menakakota's avatar
    v-menakakota
    Icon for Community Support rankCommunity Support
    1 year ago

    Hi rachk ,

    Try to check with this:

    Total Days Travelled =
    SUMX (
    SUMMARIZE (
    TravelData,
    TravelData[DepartureDate],
    TravelData[ReturnDate],
    "PeopleCount", COUNTROWS(TravelData),
    "TripDays", MAX(TravelData[Number of Days]) 
    ),
    [PeopleCount] * [TripDays]
    )
    Also please go through the updated pbix file.
    If this post was helpful, please give us Kudos and consider marking Accept as solution to assist other members in finding it more easily.
    Best Regards, 
    Menaka. 
    Community Support Team  

  • v-menakakota's avatar
    v-menakakota
    Icon for Community Support rankCommunity Support
    1 year ago

    Hi  rachk  ,

    Can you try this measure once and check.

    Total Days Travelled =

    SUMX (

        SUMMARIZE (

            TravelData,

            TravelData[DepartureDate],

            TravelData[ReturnDate],

            "@People", COUNT(TravelData[ID]),

            "@Days",   AVERAGE(TravelData[Number of Days])

        ),

        [@People] * [@Days]

    )
    Please go through the pbix file.

    If this post was helpful, please give us Kudos and consider marking Accept as solution to assist other members in finding it more easily.
    Best Regards, 
    Menaka. 
    Community Support Team  

  • v-menakakota's avatar
    v-menakakota
    Icon for Community Support rankCommunity Support
    1 year ago

    Hi rachk ,

    May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster. 

     

    Thank you. 

  • rachk's avatar
    rachk
    Icon for Helper I rankHelper I
    1 year ago

    I think that is getting closer. The ID moved into A1 etc but we have it counting each person who travelled so it needs to count. Number of days is already a measure based on the difference between the dates. 

    I dont know how you got the People part, it makes an error when I enter it, even following the above. 

  • rachk's avatar
    rachk
    Icon for Helper I rankHelper I
    1 year ago

    Also, the total days travelled would work, IF we could get the ID to calculate if there is more than 1 person on that particular date. 

  • v-menakakota's avatar
    v-menakakota
    Icon for Community Support rankCommunity Support
    1 year ago

    Hi rachk ,

    May I ask if you have resolved this issue? 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,

    Community Member.

     

  • rachk's avatar
    rachk
    Icon for Helper I rankHelper I
    1 year ago

    OK, we have lift off! I have it working but its viewed with .00 at the end...yes I am still way over my head so apologise for this. At this point if it stays here then thats ok but if I can get it to a whole number, that would be great!

     

     

     

  • v-menakakota's avatar
    v-menakakota
    Icon for Community Support rankCommunity Support
    1 year ago

    Hi rachk ,

    We really appreciate your efforts and for letting us know the update on the issue.
    It can be done by changing the values decimal places to 0. Please go through the below screenshot.



    I hope this helps to resolve your query.

    Thank you.