Forum Discussion

flying_bird's avatar
flying_bird
Frequent Visitor
1 year ago

problem with counting rows where a slicer has blanks

Hell all, i am new to power BI and got stuck with a problem. I am trying to update daily response rate in a dashboard. 

I have created three new measures: 

total invitation = countrows(Sheet1)
Cummulative responses (all responses up until the latest)

 

and then response rate as : 

divide([Cumulative Responses], [total invitation],0)
 
Then the numbers shown on dashboard are not correct:

I don't want total invitation to change based on the dates specified as they are not related. somehow Power BI thought this is what i need. Can anyone please advise?  The total invitation here should be 10 if i don't specify any other slicers...

Thanks a lot!

 

 

Need Help

5 Replies

  • Irwan's avatar
    Irwan
    Icon for Super User rankSuper User
    1 year ago

    hello flying_bird 

     

    please check if this accomodate your need.

     

    1. create a new measure with following DAX for Total Invitation

    Total Invitation = 
    CALCULATE(
        COUNTROWS('Table'),
        ALL('Table')
    )

    2. create a new measure with following DAX for Cumulative Response (exact same DAX as yours)

    Cumulative Response = 
    CALCULATE(
        COUNTROWS('Table'),
        FILTER(
            'Table',
            'Table'[Response Date]<=MAX('Table'[Response Date])&&
            not ISBLANK('Table'[Response Date])
        )
    )

    3. create a new measure with following DAX for Response Rate (exact same DAX as yours)

    Response Rate =
    DIVIDE(
        [Cumulative Response],
        [Total Invitation]
    )

    Hope this will help.
    Thank you.
    • flying_bird's avatar
      flying_bird
      Frequent Visitor
      1 year ago

      This seems not working... but thank you for trying. 

  • danextian's avatar
    danextian
    Icon for Super User rankSuper User
    1 year ago

    Hi flying_bird 

     

    For the total response rate

    CALCULATE ( COUNTROWS ( Sheet1 ), REMOVEFILTERS ( Sheet1 ) )
    • flying_bird's avatar
      flying_bird
      Frequent Visitor
      1 year ago

      Apologies. Seem like there are still issues. I think the problem is with how cummulative responses are defined.

       

      i would love to have it defined as all the responses up to a single date i picked in the slicer. 

      I expect 60% response rate for A and 40% rate for B when I select only 2/4/25 on the bar chart using the data i shared in my orginal post.

       

      The card says one rate while the chart says another.

      Can anyone please help?

       

      Thank you.

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        Hi flying_bird 

         

        Please check if this is what you want. 

        Total Cummulative Responses = 
CALCULATE(
    COUNTROWS(Sheet1),
    FILTER(
        ALL(Sheet1),
        Sheet1[Response Date] <= MAX(Sheet1[Response Date]) &&
        NOT(ISBLANK(Sheet1[Response Date]))
    )
)
        Cummulative Responses = 
CALCULATE(
    COUNTROWS(Sheet1),
    ALLEXCEPT(Sheet1,Sheet1[Slicer 1]),
    Sheet1[Response Date] <= MAX(Sheet1[Response Date]),
    NOT(ISBLANK(Sheet1[Response Date]))
)
        Response Rate = DIVIDE([Cummulative Responses], [Total Cummulative Responses], 0)

        Best regards, 

        Jing