Forum Discussion

Kate_McCulloch's avatar
Kate_McCulloch
Regular Visitor
1 year ago
Solved

Count based on disticnt value in another column

Hi Wondering if anyone could help,   I'm trying to do a total cost count for accommodation, but only want to count the value once based on the Family ID (distinct count)   If anyone could help I ...
Need Help
  • bhanu_gautam's avatar
    bhanu_gautam
    1 year ago

    Kate_McCulloch , Try using a measure for this

     

    DAX
    TotalCostByFamilyID =
    SUMX(
    SUMMARIZE(
    YourTableName,
    YourTableName[Family_ID],
    "DistinctCost",
    MAX(YourTableName[Total Cost Accommodation])
    ),
    [DistinctCost]
    )

ajaybabuinturi's avatar
ajaybabuinturi
Icon for Super User rankSuper User
1 year ago

Hi Kate_McCulloch,
I add two more rows to the sample data to get the actual Count. Try with below DAX. 

Total Cost Accommodation Count by Family = 
COUNTROWS(
    DISTINCT(
        SELECTCOLUMNS('Table', "FamilyID", 'Table'[Family_ID], "Cost", 'Table'[Total Cost Accommodation])
            )
        )


SampleData:


Result:

 

Thanks,
If you found this solution helpful, please consider giving it a Like👍 and marking it as Accepted Solution✔. This helps improve visibility for others who may be encountering/facing same questions/issues.