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