topic Re: % of Column Total Dax - PowerBI Matrix Visual in DAX Commands and Tips

% of Column Total Dax - PowerBI Matrix Visual

Anonymous — Thu, 07 Nov 2024 22:33:07 GMT

Hello,

I have the column "FTE $" that I need to show as a percent of the column total. I know of the quick way of just pulling in the same measure again and showing the value as a % of column total, but I need it formatted to 4 decimal places. Is there a way to do that or view the dax behind what the %CT FTE % is doing?

The formula for FTE $ is below.

FTE $ =

SUMX(

SUMMARIZE(

'Combined Cube Query',

'Combined Cube Query'[Speedtype and Description],

"TotalFTE", SUM('Combined Cube Query'[FTE]), -- Calculates total FTE per group

"TotalSalary", SUM('Combined Cube Query'[Salary]) -- Calculates total Salary per group

IF(

[TotalFTE] > 0, -- Checks if TotalFTE for the group is greater than 0

[TotalSalary], -- If so, adds TotalSalary to the result

0 -- Otherwise, returns 0

)

Re: % of Column Total Dax - PowerBI Matrix Visual

PhilipTreacy — Thu, 07 Nov 2024 23:20:47 GMT

Anonymous

Why not duplicate the measure and format that to 4 decimal places?

Or Measure 2 = [FTE $] and format that to 4DC?

Regards

Phil

Re: % of Column Total Dax - PowerBI Matrix Visual

VahidDM — Fri, 08 Nov 2024 01:39:32 GMT

Hi Anonymous

Yes, you can create a custom measure to calculate the percentage of the column total and format it to four decimal places. Here's how:

Create a Measure for Total FTE $:

Total FTE $ = CALCULATE([FTE $], ALL('Combined Cube Query'))

Create the Percentage Measure:

FTE % of Column Total = DIVIDE([FTE $], [Total FTE $])

Format the Percentage Measure:

Right-click the FTE % of Column Total measure in the Fields pane.
Select Format and choose Percentage.
Set Decimal places to 4.

This will display the percentage of the column total for FTE $ with four decimal places. Since you cannot view the DAX behind the built-in quick calculations, creating a custom measure gives you control over both the calculation and formatting.

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

Appreciate your Kudos!!

LinkedIn|Twitter|Blog |YouTube

Re: % of Column Total Dax - PowerBI Matrix Visual

Anonymous — Mon, 11 Nov 2024 05:48:28 GMT

Thanks for the reply from VahidDM and PhilipTreacy , please allow me to provide another insight:

Hi, Anonymous
Could you please let me know if the responses from VahidDM and PhilipTreacy have resolved your issue? If they have, kindly accept their answers as the solution

To facilitate your understanding, I have created the following example:

Currently, the decimal places are set to retain 0 digits by default.

When we select the column (field) or measure that needs modification, the navigation bar will display either Column Tools or Measure Tools:

Enter this ribbon and modify the section highlighted in red:

Here are the final results:

Of course, you can also try using the FORMAT() function. For further details, please refer to the documentation.

FORMAT function (DAX) - DAX | Microsoft Learn

Please find the attached pbix relevant to the case.

Of course, if you have any new discoveries or questions, please feel free to get in touch with us.

Best Regards,

Leroy Lu

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