cancel
Showing results for
Did you mean:
Helper IV

## Format numbers in MATRIX

I'm loading data from a datasource and using MATRIX  for output.  I need some values to be displayed in decimal numbers and some values to be displayed as %'s. Is this possible?

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support

Hi @ara_4 ,

Please refer to my .pbix file.

1. “I need only totals as totals of Actuals”

For this requirement, you can create a measure like this.

``````Measure =
VAR x =
SWITCH(
SELECTEDVALUE(Sales[CountryRegion]),
"dd", FORMAT( SUM(Sales[Sale 2014]), "Percent" ),
FORMAT( SUM(Sales[Sale 2014]), "#, 0.00")
)
VAR y =
CALCULATE(
SUM(Sales[Sale 2014]),
FILTER( ALL(Sales), Sales[CountryRegion] = MAX(Sales[CountryRegion]) && Sales[Month] = "December")
)
RETURN
IF(
HASONEFILTER(Sales[Month]),
x,y
)``````

2. Show the border of "Variance"

This requirement is currently not possible, but you can mark ‘Variance’ in other ways.

Firsr, create a measure like this.

``````Conditional format =
IF(
MAX(Sales[Month]) = "January",
"Green"
) ``````

Second, add the measure to the 'conditional formatting' label.

Best regards,
Lionel Chen

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

14 REPLIES 14
Community Champion

@ara_4

It depends if you need to have two different formats for the same measure, you can try like the example below.

``````Total Orders =
VAR C = COUNTROWS(Orders)
RETURN

IF(
C < 5 ,
FORMAT(COUNTROWS(Orders)/100,"0.0%"),
C
)``````

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

Helper IV

Hi @Fowmy ,

My dataset is as shown below:

 Allocations m\$ Actual 0.08 Strategic 0.03 Variance 0.12 Funds 3456.08

My output is a MATRIX and needs to be displayed as follows:

 Actual Strategic Variance Funds 8% 3% 12% 3456.08
Community Champion

@ara_4

You can modify the SWITCH statement to suit for your options.

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

Helper IV

Hi Fowmy,

Thanks a lot. That works !

My values do come up as you've mentioned. But my % totals now add up, instead I need it to be displayed as follows:

 Bank Funds Actuals 8% Strategic 3% Variance 5% 545 Cash Actuals 3% Strategic 4% Variance 2% 452 Total 25%

I'm using the formula you'd mentioned below to get this displayed as % for Actual, Strategic and Variance and decimal for Funds

But my total needs to only add totals(Actuals)

And also, my totals come up in Bank and Cash. (I'm using MATRIX in Power BI)

Community Champion

@ara_4

Can you share your sample data?

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

Helper IV

HI @Fowmy

Community Champion

@ara_4

You can save your files in OneDrive, Google Drive, or any other cloud sharing platforms and share the link here.
____________________________________
How to paste sample data with your question?

_____________________________________
Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

Helper IV

HI @Fowmy

This is the best I can provide:

My source Data

My required output

This is what I've done in Power BI

My Measure value2 has the formula you mentioned:

value2 = var kpw=SELECTEDVALUE('Sheet1 (2)'[invest]) return switch(kpw, "FUM",FORMAT(sum('Sheet1 (2)'[Value]),"#,00.00"), FORMAT(SUM('Sheet1 (2)'[Value]),"0.00%"))

When I then use a MATRIX, I get the values as below:

I dont need the totals in Propert, Cash , DSG and Long-Term. Also, is there a way I can highlight the borders of Variance alone as shown in the required output?

ara

Community Champion

@ara_4

Try this measure:

``````Measure =

SWITCH(
SELECTEDVALUE(DATA[Invest]),
"FUM", FORMAT( SUM(DATA[m\$]), "Currency"),
FORMAT( SUM(DATA[m\$]), "Percent")
)``````

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Power Platform October Community Highlights

Check out the top community contributors across all of the communities

#### Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors