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  

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

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. 

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

Hi  ,
Thanks for reaching out to the Microsoft fabric community forum.
 
Can you check with this measure once:

TotalPersonDaysGrouped =
SUMX (
    ADDCOLUMNS (
        SUMMARIZE (
            TravelData,
            TravelData[DepartureDate],
            TravelData[ReturnDate]
        ),
        "Days", MAXX (
            FILTER (
                TravelData,
                TravelData[DepartureDate] = EARLIER(TravelData[DepartureDate]) &&
                TravelData[ReturnDate] = EARLIER(TravelData[ReturnDate])
            ),
            TravelData[Number of Days]
        ),
        "PersonCount", CALCULATE (
            COUNTROWS (TravelData)
        )
    ),
    [Days] * [PersonCount]
)

If I misunderstand your needs or you still have problems on it, please feel free to let us know.   

Best Regards, 
Hammad. 
Community Support Team  

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

    Hi  rachk  ,
    Thanks for reaching out to the Microsoft fabric community forum.
     
    Can you check with this measure once:

    TotalPersonDaysGrouped =
    SUMX (
        ADDCOLUMNS (
            SUMMARIZE (
                TravelData,
                TravelData[DepartureDate],
                TravelData[ReturnDate]
            ),
            "Days", MAXX (
                FILTER (
                    TravelData,
                    TravelData[DepartureDate] = EARLIER(TravelData[DepartureDate]) &&
                    TravelData[ReturnDate] = EARLIER(TravelData[ReturnDate])
                ),
                TravelData[Number of Days]
            ),
            "PersonCount", CALCULATE (
                COUNTROWS (TravelData)
            )
        ),
        [Days] * [PersonCount]
    )

    If I misunderstand your needs or you still have problems on it, please feel free to let us know.   

    Best Regards, 
    Hammad. 
    Community Support Team  

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

    The "days" are already a calculation so doesnt pick it up