Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

How can i show the ratio of the Actual Plan?

Javierphang_1-1645162817784.png

 

In the above image, I have calculated the Target Plan ratio using this line of code.

DAX Formula:

Target Plan Ratio =
VAR _total =
SUMX( ALL( 'MJK' ), [Plan Listed Headcount Of Employee] )
VAR _subtotal =
SUM( 'MJK'[Plan Listed Headcount Of Employee] )
RETURN
DIVIDE( _total, _subtotal )
 
However, for the calculation of Actual plan ratio, i am not able to calculate because my actual plan column is a calculated measure. using this line of code.
 
MJK Employee Count (Actual) =
COUNTROWS(
FILTER(
ALL( 'MJK Total Employees' ),
'MJK Total Employees'[Department]
= SELECTEDVALUE( 'MJK'[Department] )
&& 'MJK Total Employees'[Section]
= SELECTEDVALUE('MJK'[Section ])
&& 'MJK Total Employees'[Sub Section]
= SELECTEDVALUE( 'MJK'[Sub Section] )
)
)

For eg. How i get the 186.35? It is calculated as 5777/31 =186.35

 

In this case, the total actual plan is 4145 and i want it to calculate as 4145/30 = 138.16.

However, the total actual column is a calculated measure as described above, therefore, i cant use the same DAX function to test it out like how i did the Target Plan Ratio.

Therefore, any way to calculate the actual ratio even if it is a calculated measure? Any help is appreciated. Thanks.

 

 

 

9 REPLIES 9
Anonymous
Not applicable

Any help?

v-henryk-mstf
Community Support
Community Support

Hi @Anonymous ,

 

According to your description, are you saying that the total number of statistics created for the measure [actual plan] is 4145, but in the final calculation, the actual total number used is 5777 corresponding to the Target plan field, is that what you mean?

Also what is your data model, if you can provide test data (remove sensitive information), I will answer for you as soon as possible.

 

How to Get Your Question Answered Quickly - Microsoft Power BI Community


Best Regards,
Henry

 

Anonymous
Not applicable

I have shared my PBI report previously, but it cant be opened as other users have requested before. 

 

Nonetheless, the 5777 is the total plan value. In this case, the total actual is 4145 but it is not shown in the table using the above code. What i want now is to calculate ratio for total actual column, is there a way to do it?

Hi @Anonymous ,

 

For your description, it should be a problem that MEASURE does not show the correct calculation results. You can refer to the following similar solutions and related blogs, hope it will help you.

Solved: Re: Sum of values in a measure with divide measure - Microsoft Power BI Community

How to Make Measures Total Correctly in Power BI Tables - ArcherPoint


Best Regards,
Henry


If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous
Not applicable

The calculation is exactly what i want, but it is only not showing the total. Unless you have a better way of calculating it, i am open to try and test it on my side. 

 

The main point is whether can i create a ratio for based on the actual column, and in this case, my actual column is a calculated measure which is why i have tried several ways and i still cant resolve them.

amitchandak
Super User
Super User

@Anonymous , Create a measure for GT

 

sumx(addcolumns(summarize (all('MJK Total Employees'), 'MJK Total Employees'[Department], 'MJK Total Employees'[Section] ), "_sum", [MJK Employee Count (Actual) ] ), [_sum])

 

then you can try % or total

 

sumx(addcolumns(summarize (allselected('MJK Total Employees'), 'MJK Total Employees'[Department], 'MJK Total Employees'[Section] ), "_sum", [MJK Employee Count (Actual) ] ), [_sum])

 

then you can try % or total

 

If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Anonymous
Not applicable

Sorry, I have tested your DAX and it is not what i want.

 

For eg. How i get the 186.35? It is calculated as 5777/31 =186.35

 

In this case, the total actual plan is 4145 and i want it to calculate as 4145/30 = 138.16.

However, the total actual column is a calculated measure as described above, therefore, i cant use the same DAX function to test it out like how i did the Target Plan Ratio.

Anonymous
Not applicable

Any idea anyone? Please help!

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors