How to calculate Grand Total Percentage in Matrix Table Column?

[UrbanCK]

Is there a DAX formula to calculate Grand Total Percentage only in the matrix Table that has drill through filter but only show the percentage in the Grand Total columns instead of every period column? 

See Matrix table in Pic #1 where I have FY Data and Period Data in the columns. I only want to show the % Total Spend in the Grand Total instead of every column in the Period Data (e.g. Q1). 

The build visualisation in Pic #2 show the different criterias in the relevant Rows and Columns. 

Pic #3 shows tha "IB Data ALL" table which is a combination of 3 different data tables consolidated.

Matrix TableVisual Matrix Table Rows and ColumnsIB Data ALL Table

[Anonymous]

Hi @UrbanCK ,
According to you, you only need to show it in the last total column and not in the quarter, right?
We can use the isinscope function to determine where we want to display the values, for example, only in the total column.

Total Spend Percentage = IF(
    NOT ISINSCOPE('Table'[Period Data])&&NOT ISINSCOPE('Table'[FY Data]),
DIVIDE(
    MAX('Table'[Total Spend]),
    CALCULATE(SUM('Table'[Total Spend]), ALL('Table')),
    0
),BLANK())

But this will show blank values, and there is no way to remove this column automatically, which is a known limitation of power bi, but we can turn off text line feeds for column headers, and then manually hide other columns by adjusting the width of the other columns so that only the values you want are shown in the Total column.
But we can turn off the text line feed for the column headers, and then manually hide the other columns by adjusting their widths, so that only the values you want are shown in the Total column.

If you still have questions, you can check the pbix file I uploaded, I hope my method can solve your problem, I would be honored if I can solve your problem!

Hope it helps!

Best regards,
Community Support Team_ Tom Shen

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

[UrbanCK]

Hi v-xingshen-msft

Thanks for this the formula seems to work however the Total Spend % in Row is not adding up to 100%.

See the matrix with the inScope formula.

 

Also how do I convert the numbers in Yellow column from decimal to percentage %?

However your recommendaiton by turning off text line feeds for column headers, and then manually hiding other columns for the Period Data Total % seems to work (see the table below)

[Anonymous]

Hi @UrbanCK ,
It looks like part of your problem has been solved, I feel very honored, if you want to show the total column as 100 percent, you just need to dax in the max for sum can be, if you want to show as a percentage, you can set up here, as shown in the figure below:

Total Spend Percentage = IF(
    NOT ISINSCOPE('Table'[Period Data])&&NOT ISINSCOPE('Table'[FY Data]),
DIVIDE(
    SUM('Table'[Total Spend]),
    CALCULATE(SUM('Table'[Total Spend]), ALL('Table')),
    0
),BLANK())

I hope these steps will complete the rest of your needs, and I would be honored if I could complete all of them!

Hope it helps!

Best regards,
Community Support Team_ Tom Shen

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

[UrbanCK]

Hi @Anonymous 

Right not sure what is wrong but still doesn't add up to 100% with the "sum" formula. 

The table visual parameters:

This is the DAX formula: 

Total % = IF(

    NOT ISINSCOPE('IB Data ALL'[Period Data])&&NOT ISINSCOPE('IB Data ALL'[FY Data]),

DIVIDE(

    SUM('IB Data ALL'[Total Spend]),

    CALCULATE(SUM('IB Data ALL'[Total Spend]), ALL('IB Data ALL')),

    0

),BLANK())

I have a manual calculation in Excel and it needs to be the % in Yellow

Looks like there is a limitation with Power BI matrix tables - not as clever as Excel Pivot Tables.

[Anonymous]

Hi @UrbanCK ,

This seems to be context related, the process of using measure is not recognizing the context correctly, if possible, could you upload your pbix file or example data, this will help you better identify your problem, looking forward to your reply!