Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Trying to group while using COUNTA

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!

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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