Power BI User Group of Philadelphia

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
Expand all | Collapse all

Help with stacked bar chart

Jump to Best Answer
  • 1.  Help with stacked bar chart

    Gold Contributor
    Posted Apr 23, 2019 03:51 PM
    Howdy:

    I have a fact table containing exactly one row for each report. Some reports (about 1 in 10) have one or more notes, which are contained in a separate table.

    I am trying to create a stacked bar chart to show the number of reports with one or more notes on top of the number of reports with no notes, but I haven't been able to figure this out. Could someone give me a nudge in the right direction?

    ------------------------------
    Thank you,
    Christopher Schnaars
    LTI
    ------------------------------


  • 2.  RE: Help with stacked bar chart
    Best Answer

    Bronze Contributor
    Posted May 02, 2019 03:30 PM
    Assuming an active relationship between these tables, which I will name 'Report' (one row per report) and 'Note'. And the cardinality of this relationship is 'Note':'Report' 1:N.

    Note count =
    COUNTROWS ( 'Note' )
    
    
    Reports with exactly one note =
    COUNTROWS (
        FILTER (
            VALUES ( 'Report'[ReportId] ),
            [Note count] = 1
        )
    )
    
    
    Reports with more than one note =
    COUNTROWS ( 
        FILTER (
            VALUES ( 'Report'[ReportId] ),
            [Note count] > 1
        )
    )​


    ------------------------------
    Greg Baldini
    Data guy
    ------------------------------



  • 3.  RE: Help with stacked bar chart

    Gold Contributor
    Posted May 03, 2019 09:54 AM
    Hi, Greg:

    Thanks for taking the time to get back to me. I started using Power BI only last month and have a ways to go.

    I eventually got this to work by creating a measure to calculate DISTINCTCOUNT of the Report ID in the Notes table. To get the number of reports with no notes, I just subtract the count above from the total count of reports. That seems to be working and preserves filter context.

    Thanks again,
    chris

    ------------------------------
    Thank you,
    Christopher Schnaars
    LTI
    ------------------------------