topic Re: Display Number and Percentage and retain Numeric value in DAX Commands and Tips

Display Number and Percentage and retain Numeric value

Silvard — Mon, 16 Dec 2024 12:42:48 GMT

I am trying to do the age old “look like excel” in which the value we are trying to replicate shows a number for said value then a percentage of the grand total. Is there a way to show both at the same time without just making a duplicate of the value i am using and while retaining the numeric value?

I suspect a calculation group would be the way to achieve this, but unsure how to implement.

Re: Display Number and Percentage and retain Numeric value

DataNinja777 — Mon, 16 Dec 2024 13:27:13 GMT

Hi Silvard ,

You can achieve this by simply formatting the measure in DAX to display both the numeric value and its percentage. Use the following DAX:

MyFormattedMeasure = VAR TotalValue = CALCULATE(SUM('YourTable'[YourColumn]), ALL('YourTable')) RETURN FORMAT(SUM('YourTable'[YourColumn]), "0.00") & " (" & FORMAT(DIVIDE(SUM('YourTable'[YourColumn]), TotalValue), "0.00%") & ")"

This measure dynamically calculates and formats the numeric value alongside its percentage of the grand total in one step.

Best regards,

Re: Display Number and Percentage and retain Numeric value

johnt75 — Mon, 16 Dec 2024 13:29:40 GMT

I did something similar with a calculation group to show the value and then then year on year % change, using the below calculation item

Value ( YoY%) = VAR _CurrentValue = SELECTEDMEASURE() VAR _PrevYearValue = CALCULATE( SELECTEDMEASURE(), DATEADD( 'Calendar'[Calendar_Date], -1, YEAR ) ) VAR _YoY = DIVIDE( _CurrentValue - _PrevYearValue, _PrevYearValue ) VAR Result = FORMAT( _CurrentValue, SELECTEDMEASUREFORMATSTRING() ) & " ( " & FORMAT( _YoY, "0.0%") & " )" RETURN Result

You could also do something similar with the Format String Expression using dynamic format strings.

Re: Display Number and Percentage and retain Numeric value

Silvard — Tue, 17 Dec 2024 03:27:00 GMT

johnt75 DataNinja777

thanks for your input to this.

I ended up finding the solution through this article https://www.sqlbi.com/articles/improving-data-labels-with-format-strings/

Specifically utilising the example:

Format String Expression for the Total Views measure in Views table