Display Number and Percentage and retain Numeric value

[Silvard]

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.

[Silvard · Accepted solution]

@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

[johnt75]

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.

[DataNinja777]

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,