Forum Discussion

acarter02's avatar
acarter02
Copper Contributor
Dec 12, 2021

Pivot Table and Chart, Percentages, and calculated field

Hello all -

 

I'm struggling with setting up a pivot table to create a pivot chart.  My data has age group and number of ER visits.  Then I have to create a new field that assigns a 1 if the number of ER visits is 1 or more visits and 0 otherwise.  I created the column in the data, but I think I need to create a calculated field that does the same.  The assignment provides a hint (HINT: The average of a 0-1 column is a percent) but that honestly doesn't make sense to me.  I've attached my data and what I have done.  Essentially, I need to be able to chart the highlighted items.  If I filter out the 0, then all of the age groups are 100% (which is not what it should be).  

 

 

I can do this easily outside of a pivot table.  I'm probably doing something stupid, lol.  Any guidance is appreciated!

 

 

excel

3 Replies

    • acarter02's avatar
      acarter02
      Copper Contributor
      Dec 12, 2021
      Thank you for the response. Yes that might work. I can see that you made the "0" series No Line. I'm wondering if it makes more sense to make the Has ER Visit a calculated field. Does the hint that is provided make sense to you? (The average of a 0-1 column is a percent).
      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Dec 12, 2021

        acarter02 

        You didn't mention on which version of Excel. If data model is available I'd work with it.

        You may use measure

        Has ER Visit, %:=VAR allVisits =
    COUNTROWS ( Range )
VAR ERvisits =
    CALCULATE ( COUNTROWS ( Range ), Range[Has ER Visit] = 1 )
RETURN
    DIVIDE ( ERvisits, allVisits )

        to build chart like

Resources