topic % Based on Grand Total of the category in DAX Commands and Tips

% Based on Grand Total of the category

Anonymous — Tue, 15 Dec 2020 21:44:57 GMT

Hello,

I am hoping to get some directions as to how to calculate % planned and actual based on certain point of time.

For example:

I want the slicer to show the calculation in % based on the specified time. So, for example based on the sample data (attached) if the Slicer is set for July. I want planned to show 33% for Emp 1 and actual to show 67% for Emp 1.

I was able to get that for planned using the RELATED function. But, I am unable to replicate that for the actuals as it's keep giving me circular reference error.

Is there a way to fix it or is there a better way to do it?

I have attached my sample .pbix file too.

Thank you in advance for the help.

Re: % Based on Grand Total of the category

amitchandak — Wed, 16 Dec 2020 07:11:25 GMT

Anonymous , Try a measure like

Planned % Measure = DIVIDE([CompletedCount],[PlannedCount])

Please find the file attached after signature

Re: % Based on Grand Total of the category

Anonymous — Thu, 17 Dec 2020 16:57:29 GMT

amitchandak , thank you for your response. Unfortunately it didn't help me fix the issue as at times when the completion was higher than the planned for the particular month it was showing a % higher than 100 where as what I was looking for is a % based on YTD planned. But, I was able to find a way to find the solution using a different method listed below

Re: % Based on Grand Total of the category

Anonymous — Thu, 17 Dec 2020 17:03:25 GMT

The way I got around to fixing this is by

1) Creating a column in the relevant table to get the total count using Earlier function based on the category you want. In my case it was the total count by employee

Total Count = Calculate(Count)('Your table name'[Column name]), Filter(Your table name'[Column name]=Earlier(Your table name'[Column name])))

2) Use the Max function to find the max count by employee

3) Use Divide function to get the % at the point in time with the denominator being the Grand Total