Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
In the above image, I have calculated the Target Plan ratio using this line of code.
DAX Formula:
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.
Any help?
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
Hello, this is the sample file
Excel files on google drive
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.
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.
@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.
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.
Any idea anyone? Please help!
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
86 | |
84 | |
83 | |
67 | |
49 |
User | Count |
---|---|
131 | |
111 | |
97 | |
71 | |
67 |