PowerBI total sum is incorrect

[hushpuppies]

Hi,

Please could you let me know why is the total % Budget Spent is incorrect?

% Budget spent formula = IFERROR (Actual / Approved Funding,0)

How do I hide the total sum for % Budget Spent column?

Thank you.

[v-fenling-msft · Accepted solution]

Hi, @hushpuppies 

I am glad to help you. 

According to your description, you want to know why is the total % Budget Spent is incorrect? 

If I understand you correctly, then you can refer to my solution. 

Since you didn't give a dataset for testing, I assumed some data for testing myself. 

The values in the Matrix are all an aggregated value, so consider converting the data into a Measure for your calculations. 

You can refer to the Measure I created as needed. 

Actual2 = 
VAR _Category =
    SELECTEDVALUE ( 'Table (2)'[Category] )
VAR _SubCategory =
    SELECTEDVALUE ( 'Table (2)'[SubCategory] )
VAR _result =
    IF (
        NOT ISINSCOPE ( 'Table (2)'[SubCategory] ),
        SUMX ( FILTER ( 'Table', 'Table'[Category] = _Category ), [Actual] ),
        SUMX (
            FILTER (
                'Table',
                'Table'[Category] = _Category
                    && 'Table'[SubCategory] = _SubCategory
            ),
            [Actual]
        )
    )
RETURN
    _result

Approved Funding2 = 
VAR _Category =
    SELECTEDVALUE ( 'Table (2)'[Category] )
VAR _SubCategory =
    SELECTEDVALUE ( 'Table (2)'[SubCategory] )
VAR _result =
    IF (
        NOT ISINSCOPE ( 'Table (2)'[SubCategory] ),
        SUMX ( FILTER ( 'Table', 'Table'[Category] = _Category ), [Approved Funding] ),
        SUMX (
            FILTER (
                'Table',
                'Table'[Category] = _Category
                    && 'Table'[SubCategory] = _SubCategory
            ),
            [Approved Funding]
        )
    )
RETURN
    _result

Then create another Measure to calculate the '% Budget spent formula' so you don't have to worry about it calculating the wrong values. 

% Budget spent formula2 = 
IFERROR ( [Actual2] / [Approved Funding2], 0 )

Here are my test results : 

If you want to hide a column or a Measure, you can decide whether to make the column or Measure visible by selecting the corresponding column or Measure and setting the switch with the little eye like in the picture. 

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,
Fen Ling,

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

[foodd]

Hello @hushpuppies, thank you for sharing a question with the Community.  The following in informational.  Please remember to adhere to the decorum of the Community Forum when asking a question.

Please provide your work-in-progress Power BI Desktop file (with sensitive information removed) that covers your issue or question completely in a usable format (not as a screenshot). You can upload the PBIX file to a cloud storage service such as OneDrive, Google Drive, Dropbox, or to a Github repository, and then share a file’s URL.

https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

This allows members of the Forum to assess the state of the model, report layer, relationships, and any DAX applied.