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

 

 

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  

34 Replies

  • Ashish_Excel's avatar
    Ashish_Excel
    Icon for Solution Supplier rankSolution Supplier
    1 year ago

    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's avatar
      rachk
      Icon for Helper I rankHelper I
      1 year ago
      DepartureDateReturnDateNumber of DaysIDDesired Outcome (Total Days Travelled)
      16/03/2025 5:00:00 PM20/03/2025 8:00:00 PM414
      8/04/2025 7:00:00 AM8/04/2025 3:00:00 PM122
      14/04/2025 6:00:00 AM14/04/2025 4:00:00 PM122
      1/05/2025 6:30:00 AM2/05/2025 10:00:00 AM111
      14/05/2025 12:00:00 AM14/05/2025 12:00:00 AM122
      15/05/2025 10:00:00 AM17/05/2025 2:00:00 PM224
      18/05/2025 2:19:00 PM24/05/2025 7:13:00 AM515
      22/05/2025 8:00:00 AM26/05/2025 6:00:00 PM414
      25/05/2025 4:00:00 PM28/05/2025 8:00:00 PM325
      27/05/2025 6:30:00 AM27/05/2025 7:40:00 PM122
      27/05/2025 7:00:00 AM28/05/2025 8:00:00 PM111
      28/05/2025 12:00:00 PM30/05/2025 1:00:00 PM212
      4/06/2025 8:30:00 AM6/06/2025 5:00:00 PM212
      5/06/2025 1:30:00 AM6/06/2025 9:00:00 AM111
      • Ashish_Excel's avatar
        Ashish_Excel
        Icon for Solution Supplier rankSolution Supplier
        1 year ago

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

  • pankajnamekar25's avatar
    pankajnamekar25
    Icon for Super User rankSuper User
    1 year ago

    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's avatar
      rachk
      Icon for Helper I rankHelper I
      1 year ago

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

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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