Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi.
My first post.
So I have a matrix and on this matrix I am trying to calculate in DAX a field called % of Premium.
Please see pic above, for 2017 the % of Premium value for the Liability section should be 22.14% (175607198 / 793274434)
However given the following DAX formula I get 33.61% (175607198 / 52638561)
% of Premium = SUM('Agency Scorecard 3'[Earned Premium]) / CALCULATE(SUM('Agency Scorecard 3'[Earned Premium]), ALLSELECTED())
You may be asking why I'm not using SQL to do this and the fact is that running this calculation over SQL will get me spurious results. I really need to use the row total Power BI is using.
Please help.
Solved! Go to Solution.
Here's what worked but I don't understand why!
I just added the red part and all worked.
% of Premium = DIVIDE(SUM('Agency Scorecard 3'[Earned Premium]), CALCULATE(SUM('Agency Scorecard 3'[Earned Premium]), ALLEXCEPT('Agency Scorecard 3','Agency Scorecard 3'[Loss Year]),'Agency Scorecard 3'[Summary Line ID],'Business Division'[BusinessDivisionID], VALUES('Business Division'[BusinessDivisionID])), 0)
Thanks all for your help in getting me on the right track.
Here's the post that explains why the Values function works so now I know.
https://community.powerbi.com/t5/Desktop/ALLEXCEPT-only-working-on-visible-row-context/td-p/18030
You keep adding for all your report (might work with page/visualisation filters I don;t know) filters as follows:
% of Premium = DIVIDE(SUM('Agency Scorecard 3'[Earned Premium]), CALCULATE( SUM('Agency Scorecard 3'[Earned Premium]), ALLEXCEPT('Agency Scorecard 3','Agency Scorecard 3'[Loss Year]),'Agency Scorecard 3'[Summary Line ID],'Business Division'[BusinessDivisionID], VALUES('Business Division'[BusinessDivisionID]), VALUES('Agent'[Agent Alternate 1]), VALUES('Agent'[Agent Alternate 2]), VALUES('Sub Producer'[Alt Sub Producer 2]), VALUES('Managing Unit'[Managing Unit Name]), VALUES(Office[Office]), VALUES('Product Line'[Product Line Name]), VALUES(Underwriter[Underwriter Name]) ),0)
Hi @Sureshot
Instead of ALLSELECTED()
could you try
Allexcept(TableName,TableName[LossYear])
Hi. Thanks but that gave 7.28% for the example I gave. I donlt even know how 7.28% is caculated.
Hi @Sureshot
Actually ALLSELECTED works on Totals. It does not impact Row and Column filters
Could you share your file via googledrive or onedrive?
I don;t yet have either of thse accounts but I can look into setting one up and sending my pbix file.
The thing is here I am looking to get row totals. AllSelected works great for columns but now row columns i.e. I need to get the 793,274,434 figure as the divisor.
Have you tried using:
% of Premium = SUM('Agency Scorecard 3'[Earned Premium]) / CALCULATE(SUM('Agency Scorecard 3'[Earned Premium]), ALLSELECTED('Agency Scorecard 3'[Earned Premium]))?
For the row you've selected, you need to tell it what should remain unfiltered. Your expression (ALLSELECTED()) is telling it to unfilter everything which results in the % of total, not row.
Thanks I tried that but it gave me 100% as 175607198 divided by itself is 1.
Is there a way to calculate the row total dynamically in DAX or somehow grab the 793274434 number as the divisor?
So I've tried the following which works if I change my underlying query to only point at the business division I have set in the report filter. Also if I remove that report filter it works. What is that about?
% of Premium = SUM('Agency Scorecard 3'[Earned Premium]) / CALCULATE(SUM('Agency Scorecard 3'[Earned Premium]), ALLEXCEPT('Agency Scorecard 3','Agency Scorecard 3'[Loss Year]),'Agency Scorecard 3'[Summary Line ID])
Sorry that didn't work for you. The article at https://www.sqlbi.com/articles/understanding-allselected/ helped me to better understand how to generate a correct row total DAX expression. Especially when I realized you could have multiple ALLSELECTED (Table[Column]) statements in a CALCULATE statement. I just had to play around a bit and was able to solve it with my set up. Since I don't know how your data is really set up, I can't help you.
Here's what worked but I don't understand why!
I just added the red part and all worked.
% of Premium = DIVIDE(SUM('Agency Scorecard 3'[Earned Premium]), CALCULATE(SUM('Agency Scorecard 3'[Earned Premium]), ALLEXCEPT('Agency Scorecard 3','Agency Scorecard 3'[Loss Year]),'Agency Scorecard 3'[Summary Line ID],'Business Division'[BusinessDivisionID], VALUES('Business Division'[BusinessDivisionID])), 0)
Thanks all for your help in getting me on the right track.
Here's the post that explains why the Values function works so now I know.
https://community.powerbi.com/t5/Desktop/ALLEXCEPT-only-working-on-visible-row-context/td-p/18030
You keep adding for all your report (might work with page/visualisation filters I don;t know) filters as follows:
% of Premium = DIVIDE(SUM('Agency Scorecard 3'[Earned Premium]), CALCULATE( SUM('Agency Scorecard 3'[Earned Premium]), ALLEXCEPT('Agency Scorecard 3','Agency Scorecard 3'[Loss Year]),'Agency Scorecard 3'[Summary Line ID],'Business Division'[BusinessDivisionID], VALUES('Business Division'[BusinessDivisionID]), VALUES('Agent'[Agent Alternate 1]), VALUES('Agent'[Agent Alternate 2]), VALUES('Sub Producer'[Alt Sub Producer 2]), VALUES('Managing Unit'[Managing Unit Name]), VALUES(Office[Office]), VALUES('Product Line'[Product Line Name]), VALUES(Underwriter[Underwriter Name]) ),0)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
96 | |
94 | |
82 | |
70 | |
64 |
User | Count |
---|---|
116 | |
106 | |
94 | |
79 | |
72 |