How to modify Total in Power BI Table or Matrix

Howdy Everyone,

Many clients using Microsoft Excel previously for data storytelling about their business after Power BI came to the market it has lot of flexibility, customization, visual improvements and many features. Power BI has lot of features which Excel can’t able to do also vice versa Power BI can’t be the cent percent replacement there is some of features in excel is not directly able to do it in Power BI. Hope Power BI team will update in coming days.

In this use case we will try to modify the column by cell level like Excel in Power BI. I have created one sample data and lets try this in step by step. Here is the sample data looks like..

I want to calculate the Total Budget in DAX, well that is a quick thing you can add the budget columns in the column field to the Table or Matrix visual, Power BI automatically calculates the SUM of the value if the data was in number datatypes or currency or we can create separate measure for those value columns like this below screenshot

Now I want Approved budget in the data that is also quite easy like the above
Just create the measure with the Approved logic
Approved = CALCULATE([Budget], Table1[ID]= 1 || Table1[ID] = 3 || Table1[ID] = 5 )

Here is the Complexity,
I want to show the unapproved value for the cities and show the total unapproved values in Remaining column and also need to show approved budget values in the same column and without adding those values in the Totals also the values needs to be highlighted.

Nice, The above use case we can easily achieve in Excel.
In Excel we can control the data by cell level.

What if the end user wants the same functionality in Power BI.

Lets create Remaining budget value measure by difference of Budget – Approved
Remaining = var t = [Budget] – [Approved] return t

Now the Total shows as expected by instead of 0, We want to show the Approved column values too. Crazy? lets solve this by DAX

Remaining =
var t = [Budget] – [Approved]
return
IF(t = 0,
“₹” & FORMAT([Budget], “#,##,###”, “en-IN”),
“₹” & FORMAT(t,”#,##,###”, “en-IN”) )
By changing the datatype of number or currency to text then it won’t added as Total in the Table or Matrix.

Now We have to condition format those values
Cond Format = IF([Approved] <> BLANK(), “#FFFF66”, BLANK())
add the above measure to the remaining measure conditional formatting.
Finally,

Now Still one issue is there,
even after I change the number comma format to local still its showing in United States,
Appreciated who will help this to change Indian Number system.
Thanks in advance!