Exclude Data in CY Total When PY Data is Missing

[mh_system]

Hello,

I have headcount data from multiple schools. In the report I have 2 cards showing the total for the current year and the percent change from prior year to this year. I want to calculate the total and the total percent change from the prior year to the current year but exclude in the calculation the current year data of schools with missing  prior year data.

School    PY      CY

1             100   125

2             110   100

3             125   110

4                      157

5             65     100

I would like to calculate the total for CY as 435 (leaving out school 4 because there isn't a PY number for that school) and not 592. Then I would like to calculate the percent change of CY from PY, 45% and not 97.3%. These total calculations, CY total without school data from missing data PY and percent change from PY, will be in cards, not in the column totals.

Thanks,

[v-easonf-msft]

Hi, @mh_system 

In your sample table, how would you calculate the percent change of CY from PY? Can you explain in detail how to get the '45%' ?

Best Regards,
Community Support Team _ Eason

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.

[mh_system]

Here is the link to the data (it's closer to the real data than what was posted above.) Below that is the link to the PBI report I am trying to create with this data.

Data link: https://docs.google.com/spreadsheets/d/1eNMRo2QDSSa6nLrj5XzoLrAL0ExDbbohnGtLYJTatyo/edit#gid=0

PBI link: https://app.powerbi.com/view?r=eyJrIjoiOTBlMTUwMzMtODAxMi00NzBjLThmM2EtY2YwZmU5MzVlNDE0IiwidCI6Ijc4Z...

I don't want the -.6% to show but the -11.8% to show without having to manually exclude the schools with missing data.

Thanks,

[Ashish_Mathur]

I cannot download the PBI file from that link.

[mh_system]

Can you tell me how to share the pbix file? My options seem to be to Share in my organization, Share to People with existing access, or Specific people. Thanks,

[Ashish_Mathur]

Upload it to Google Drive and share the download link.

[mh_system]

Thank you, this is something good toknow I can do.

https://drive.google.com/file/d/1M_8j8c9kOV0v4odKUk-tvKXFZWhRKbyD/view?usp=sharing

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[mh_system]

Awesome, many thanks! Melissa

[Ashish_Mathur]

You are welcome.