Trying to group while using COUNTA

[Anonymous]

I am a very new Power BI user. I am trying to get the stacked bar below to show the percent compliant/noncompliant for the various different sites, but it is giving me the total for all sites. I am using the COUNTA function. I thought it might be an issue with how my model is set up. Essentially, I want COUNTA to group by the site name and compliant/noncompliant. The highlighted model below shows where the tables are joined.  Any thoughts? Thanks!

[Anonymous · Accepted solution]

HI @Anonymous ,

It seems like your tables have linked with chain relationship mapping with 'cross' filter direction, I haven't found any issues on relationship design. (it may be related to table records mapping)
Maybe you can need to create a summary table to summary these table records to one table based on relationship keys or write measures to manually lookup records from different tables and summary them.

Measure formula:

Measure =
VAR currSiteID =
    VALUES ( Sites[SiteID] )
VAR _monitoringList =
    CALCULATETABLE (
        VALUES ( MonitoringReview[MonitoringID] ),
        FILTER ( ALLSELECTED ( MonitoringReview ), [SiteID] IN currSiteID )
    )
VAR _itemList =
    CALCULATETABLE (
        VALUES ( MonitoringItem[ItemID] ),
        FILTER ( ALLSELECTED ( MonitoringItem ), [MonitoringID] IN _monitoringList )
    )
VAR _resultList =
    CALCULATETABLE (
        VALUES ( MonitoringReviewItem[ResultID] ),
        FILTER ( ALLSELECTED ( MonitoringReviewItem ), [ItemID] IN _itemList )
    )
RETURN
    CALCULATE (
        COUNTA ( MonitoringReviewType[ResultID] ),
        FILTER ( ALLSELECTED ( MonitoringReviewType ), [ResultID] IN _resultList ),
        VALUES ( MonitoringReviewType[NonCompliant] )
    )

Notice: use Sites[SiteID] as axis, MonitoringReviewType[NonCompliant] as legend, measure as value to create chart.
Regards,
Xiaoxin Sheng