How to aggregate project variances and do calcs at the project category level

[wlaughl]

Hello, I have a table that includes project-level data, as well as a field for Project Category. I also have a measures table with Forecasted Spend and Actual Spend. Both tables are in a PBI dataset using Direct Query mode. 

I want to do the following:

- Aggregate Forecasted Spend and Actual Spend at the Project Category level, instead of the Project level

- Calculate the % difference between Forecasted Spend and Actual Spend, again at the Project Category level

- Evaluate each Project Category's % difference between Forecasted Spend and Actual Spend, to determine which Project Category met a +/- 5% threshold

- Calculate # and % of Project Categories that met/did not meet the target threshold

- Be able to filter on Project Categories that met/did not meet the target threshold

I considered building a summary table with SUMMARIZECOLUMNS to aggregate the data to the Project Category level, but I understand that credentials issues can cause issues with data refresh in the Power BI service using DirectQuery mode. 

Thanks in advance for the guidance! 

[wlaughl]

Thank you @Amit I was able to create that measure successfully. However it only gives me a "1" for each project category that met the +/-5% criteria in a tabular visual. How do I write a measure/column that will add up all those 1's to get a total number of projects that met the criteria, and then divide that # by the total # of projects to get a % Met? Thanks! 

[amitchandak]

@wlaughl , You need measure like below one, you can use summarize in place of values , assumed - Spend and forecast measure

var _diff =divide(abs([Forecast]-[Spend]),[Forecast])
return
Countx(filter( Values([Project Category]) , _diff<=.05),[Project Category])