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  

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.

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

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's avatar
    Ashish_Excel
    Icon for Solution Supplier rankSolution Supplier
    1 year ago

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

    • 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