topic Re: Troubleshooting Percent calculation - Divide row total by total for all rows in matrix in DAX Commands and Tips

Troubleshooting Percent calculation - Divide row total by total for all rows in matrix

Anonymous — Wed, 31 Jan 2024 18:10:01 GMT

I am creating a measure which calculates the percentage of a total and applies it to a matrix.

I am then dividing the calculation of Total_Market_Value_Base by the sum total of all rows and that is represnted as the # % column.

# % = VAR Total_Market_Value_Base = SUM(HoldingsTDMaster[Market Value (Base)]) + (SUM(HoldingsTDMaster[Accrued Interest]) * SUM(HoldingsTDMaster[FX to Target])) RETURN DIVIDE( Total_Market_Value_Base, CALCULATE( Total_Market_Value_Base, ALLSELECTED(HoldingsTDMaster) ) )

As you can see the visual displays 1.00 for every row. I included the variable Total_Market_Value_Base in the column #%Test so you can see the number I want divided. #%Test should be divided by the total of all rows in the matrix, but it seems like with #% it is not dividing by all rows but just by just it's own row. Is ALLSELECTED the wrong function to be using?

Here is my model. There is a 1:many relationship between the table for Account Name and the rest of the fields. Sector comes from HoldingsTDMaster as well as all the fields for the calculation. Any help is much appreciated.

Re: Troubleshooting Percent calculation - Divide row total by total for all rows in matrix

Anonymous — Thu, 01 Feb 2024 07:11:34 GMT

Hi Anonymous

Perhaps you could modify the formula to look like this:

If there is still a problem, please provide sample data and the result you are hoping for: How to provide sample data in the Power BI Forum - Microsoft Fabric Community . Or show it as a screenshot or pbix. Please remove any sensitive data in advance. If uploading pbix files please do not log into your account.

Best Regards,
Community Support Team _Yuliax

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

Re: Troubleshooting Percent calculation - Divide row total by total for all rows in matrix

Anonymous — Thu, 01 Feb 2024 16:12:06 GMT

Hello, thanks for responding. This doesn't really give me what I need. I'm looking for the Total_Market_Value_Base of the row in the matrix, divided by the sum total of Total_Market_Value_Base for all rows within a sector category. This solution is just dividing Total_Market_Value_Base by a count of the rows in HoldingsTDMaster.

https://drive.google.com/file/d/1RifVPqfWGviFr793luwbCdMLPezTsbqE/view?usp=sharing

The link is a pbix with some de-identified sample data and some additional notes. Thank you very much for your help.

Re: Troubleshooting Percent calculation - Divide row total by total for all rows in matrix

gmsamborn — Thu, 01 Feb 2024 18:15:02 GMT

Hi Anonymous

Would these measures help?

Total_Market_Value_Base = SUM( HoldingsTDMaster[Market Value (Base)] ) + ( SUM( HoldingsTDMaster[Accrued Interest] ) * SUM( HoldingsTDMaster[FX to Target] ) ) # % = DIVIDE( [Total_Market_Value_Base], CALCULATE( [Total_Market_Value_Base], ALLSELECTED( HoldingsTDMaster ) ) )

Let me know if you have any questions.

Support Forum Version.pbix

Re: Troubleshooting Percent calculation - Divide row total by total for all rows in matrix

Anonymous — Thu, 01 Feb 2024 18:26:43 GMT

Hi gmsamborn. thanks for your reply. This got me a lot closer, I am now getting a different result for each row.

I'm still struggling though because the #% column isn't totaling to 100. I think when doing the CALCULATE() for the denominator to divide by, the measure will total the data for each field of the measure, then calculate [Total_Market_Value_Base], but I need it to perform that calculation for each row, and then sum the results for each row, and divide by that number. Can you help?

I updated the sample data here with your suggested measure. https://drive.google.com/file/d/1RifVPqfWGviFr793luwbCdMLPezTsbqE/view?usp=drive_link

Re: Troubleshooting Percent calculation - Divide row total by total for all rows in matrix

gmsamborn — Thu, 01 Feb 2024 18:45:19 GMT

Hi Anonymous

In your original version of [# %], you were using a variable instead of an expression or measure in your denominator. That would be the reason you were getting 1 for every row. (ie. you can't recalculate a variable).

The only thing I did was split your measure out into 2 measures. That means there was a separate problem in your original measure (or what is the new measure [# Total_Market_Value_Base] ).

I'll take a look at that series of SUMs but you would probably have as much luck.

Re: Troubleshooting Percent calculation - Divide row total by total for all rows in matrix

Anonymous — Thu, 01 Feb 2024 18:56:51 GMT

I was messing with the measures and figured it out, I needed to be adding some iterators at different stages to get it to aggregate on the right level. Here are the two measures I ended up with. Thanks again for your help.

# % = DIVIDE( [# Total_Market_Value_Base], CALCULATE( SUMX(HoldingsTDMaster,[# Total_Market_Value_Base]), ALLSELECTED(HoldingsTDMaster) ) )

# Total_Market_Value_Base = SUMX(HoldingsTDMaster,[Market Value (Base)] + ([Accrued Interest] * [FX to Target]))

Re: Troubleshooting Percent calculation - Divide row total by total for all rows in matrix

gmsamborn — Thu, 01 Feb 2024 19:10:46 GMT

Anonymous

After taking a look at your measure, I thought this might be more like what you want.

ORIGINAL

# Total_Market_Value_Base = SUMX( HoldingsTDMaster, [Market Value (Base)] ) + ( SUMX( HoldingsTDMaster, [Accrued Interest] ) * SUMX( HoldingsTDMaster, [FX to Target] ) )

NEW

# Total_Market_Value_Base = SUMX( HoldingsTDMaster, [Market Value (Base)] + ([Accrued Interest] * [FX to Target]) )

Let me know if that is what you meant.

Re: Troubleshooting Percent calculation - Divide row total by total for all rows in matrix

Anonymous — Thu, 01 Feb 2024 19:05:08 GMT

You were on the right track, take a look at my last post to see what I ended up with.

Re: Troubleshooting Percent calculation - Divide row total by total for all rows in matrix

gmsamborn — Thu, 01 Feb 2024 19:06:41 GMT

Glad you were able to figure it out.