Compare % of total from one fiscal year to the next

[rkcasey]

So I have data that include project information - hours worked, application impacted. I want to filter the projects to only include some types and then create a side by side bar graph with axis for the application, legend for fiscal year (comparing last year to this one) and the value percent of total.

I have no issues with the graph when I do it with hours worked and compare quarter to quarter but I want to compare the % for all of last year to the % for this year that is complete

I can not figure out how to write the measure for the % of total. I did write measure for the FY total that works

FY17 Total hours = CALCULATE(sum('Task data'[Hours Worked]),FILTER('Task data',RELATED('Fiscal Year'[Fiscal Year])="FY17")) but I seem to run into issues with filtering when I divide. Any ideas?

[v-yuezhe-msft]

@rkcasey,

What issues do you get with filtering? Please share sample data of your scenario and post expected result here so that we can provide you appropriate DAX.

Regards,
Lydia Zhang

[rkcasey]

That is an example of what I want.

Here is an example of the summary data that was used above.

Primary System	Hours Worked FY17	Hours Worked FY18
Middleware	15448	4300
Ordering Platform	33180	11137
Compliance Center	8089	1948
EDW	2536	766
Infrastructure	10782	3298
Printing App	14327	0
MoveIt	1335	538
Order Generator	8649	2271
Other	171819	42209
Wishbone	28746	7569

The raw data include project name, type of project, project manager, Primary system, resource name, hours worked, month worked. I have another table that maps the month to the fiscal year. After I get the FY attached to the hours in excel I just do a pivot table (like above), show rows as % of column, pop it up on a bar chart and poof. I can visually compare the % from the whole of the last fiscal year to the % for just the first quarter of this fiscal year.

In Power BI I can easily create a graph like this showing one FY data by selecting show % of grand total but if I filter to show both years in the data they end up joined together and it won't accept FY in the legend to split them out. I know my data well but I am not a programer so my DAX is weak. I would guess that my DAX would need to select each row that is in FY17 and divide by the total of all the rows in FY17 (and show it as a %) and do it is such a way that any filters or selection that apply on the report work. Sometimes I want to group by the primary system, other times by type of project, etc. THANKS

[v-yuezhe-msft]

@rkcasey，

In Power BI Desktop, you can show value as "Pecent of grand total" . If you still get issues, please help to post raw data of your tables.




Regards,
Lydia Zhang