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

[Anonymous]

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.  

[Anonymous]

Hi @Anonymous 

Perhaps you could modify the formula to look like this:

# % = 
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,
    COUNTROWS(HoldingsTDMaster)
)

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.

[Anonymous]

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.

[gmsamborn · Accepted solution]

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

[Anonymous]

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

[gmsamborn]

@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.

[Anonymous]

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

[gmsamborn]

Glad you were able to figure it out.

[gmsamborn]

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.

[Anonymous]

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]))