Forum Discussion
Calculate Percent based on different FY measures
Greg,
Thank you for your response. This is my first post to any forum like this so this is all new to me. I've read your post and did try to include in my original post as much as I could about the structure of my workbook, but did not include any data or an image of my desired visualization. Is there a way to upload my smaple pbix file? If not, I will copy and paste the necessary data from the tables in my Power BI workbook.
Charlie
Greg,
I recently added a link to my original messasge so responders can download the sample pbix file and see the model, data and visualization. If you look at the visiualiztion you will see it is a matrix with a couple of levels of drill in on the rows. If drilled into the lowest level, which is how should appear when you download the pbix, you will see three fiscal years that display monthly YTD amounts and percent of total. The rows for 2016 and 2018 percentages are calculated properly. The 2017 row, however, is incorrect and appears to be using the same total as 2016 so I think the issue is with the third "if" statement in the YTD_PCT measure, but I certainly cannot figure it out. I have been working this for a couple of weeks (PT) and would greatly appreciate any assistance.
Charlie
v-huizhn-msftMicrosoft Employee
Hi ccrossmd,
Why the value of 2017 is uncorrect? The percentage is larger than 100%?
Thanks,
AngeliaAngelia,
For 2016 and 2017 the final YTD_PCT amount should be 100%. Each line represents a monthly cumulative YTD amount for a fiscal year. The percent needs to reflect the monthly YTD amount divided by the total amount for that fiscal year. Looking at the matrix in the sample pbix file, the Aug YTD_EXP for 2017 for Div1, C1 = 642,124 and this amount should be divided by the total for 2017 771,734, which is the amount in the SEP YTD_EXP column. Therefore, the YTD_PCT for Aug 2017 for Div1, C1 should be 83.2% (642,124 / 771,734) and not the 78.4% as reflected in the matrix. The 78.4% represents the Aug 2017 YTD_EXP of 642,124 divided by the total for 2016 which is 984,604.
What makes this complicated, to me atleast, is that the percent for 2016 & 2017 needs to be calculated on the total expenditures for each fiscal year while 2018 is calcualted on total annual budget.
Any insight on how to change the YTD_PCT measure's formula would be greatly appreciated.