Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi everyone,
I'm certain that a simple solution to my query exists, but I can't seem to figure it out at the moment!
I have a table that holds finance data called all_relevant_transactions. On this table I have several Calculated Fields that work out various totals. An example is shown below:
What I really need is the total for each column to show the total of all the rows and not the overall total relating to the Calculated Field. I suspect that this is something to do with the way that I've created the Calculated Fields themselves, but I'm unable to work out what I've done wrong!
Any help would be much appreciated.
Many thanks
Jon
Solved! Go to Solution.
Hi @jonclay ,
Totals not showing correctly is a very common thing in Power BI. Maybe you should know ISINSCOPE function and ALL or ALLEXPECT function.
ISINSCOPE function (DAX) - DAX | Microsoft Learn
ALL function (DAX) - DAX | Microsoft Learn
ALLEXCEPT function (DAX) - DAX | Microsoft Learn
Since I can't see your data, here's a sample for your reference.
You can observe that the totals of the two calculated columns are not the same.
The DAX formula of the two calculated columns:
% =
IF (
ISINSCOPE ( 'Table'[School] ),
DIVIDE ( [Count], [Total count 2] ),
DIVIDE ( [Total count 2], CALCULATE ( [Count], ALL ( 'Table' ) ) )
)
% 2 = DIVIDE ( [Count], [Total count 2] )
Best regards,
Yadong Fang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @jonclay ,
Totals not showing correctly is a very common thing in Power BI. Maybe you should know ISINSCOPE function and ALL or ALLEXPECT function.
ISINSCOPE function (DAX) - DAX | Microsoft Learn
ALL function (DAX) - DAX | Microsoft Learn
ALLEXCEPT function (DAX) - DAX | Microsoft Learn
Since I can't see your data, here's a sample for your reference.
You can observe that the totals of the two calculated columns are not the same.
The DAX formula of the two calculated columns:
% =
IF (
ISINSCOPE ( 'Table'[School] ),
DIVIDE ( [Count], [Total count 2] ),
DIVIDE ( [Total count 2], CALCULATE ( [Count], ALL ( 'Table' ) ) )
)
% 2 = DIVIDE ( [Count], [Total count 2] )
Best regards,
Yadong Fang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.