Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

Hi @Anonymous ,

 

Please refer to my .pbix file.

v-lionel-msft_0-1597027714082.png

 

 

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.

v-lionel-msft_1-1597028010290.png

 

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.

View solution in original post

14 REPLIES 14
Greg_Deckler
Super User
Super User

@Anonymous - Very dependent on what you are doing, if the two different formats are different measures/columns or not. You may find some success using FORMAT but that turns things to text. The technique here might also help: https://community.powerbi.com/t5/Quick-Measures-Gallery/Chelsie-Eiden-s-Duration/m-p/793639#M389


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Fowmy
Super User
Super User

@Anonymous 

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 🙂

YouTube, LinkedIn

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Anonymous
Not applicable

Hi @Fowmy ,

 

Thanks for your response.

 

My dataset is as shown below:

Allocations

m$
Actual0.08
Strategic0.03
Variance0.12
Funds3456.08
  

 

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

 

ActualStrategicVarianceFunds
8%3%12%3456.08

@Anonymous 


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

Fowmy_0-1596876743860.png

________________________

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 🙂

YouTube, LinkedIn

 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Anonymous
Not applicable

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
   Actuals8% 
  Strategic3% 
  Variance5%545
Cash  
  Actuals3% 
  Strategic4% 
 Variance2%452
Total25% 

 

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)

 

 

@Anonymous 

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 🙂

YouTube, LinkedIn


Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Anonymous
Not applicable

HI @Fowmy 

 

I dont have access to upload any sample data, would you pass me your email?

@Anonymous 


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?
How to get your questions answered quickly?

_____________________________________
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 🙂

YouTube, LinkedIn

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Anonymous
Not applicable

HI @Fowmy 

 

This is the best I can provide:

 

My source Data

ara_4_0-1596889175712.png

 

My required output

 

ara_4_1-1596889224676.png

 

 

This is what I've done in Power BI

ara_4_3-1596889302613.png

 

 

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:
 
 
 
ara_4_0-1596889581799.png

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

 

 

 

@Anonymous 

Try this measure:

Measure = 

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

Fowmy_0-1596891398128.png

 

________________________

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 🙂

YouTube, LinkedIn

 




Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Anonymous
Not applicable

When I used the formula you had mentioned, I still get values in Cash, DSG and Long-Term

 

ara_4_0-1596894310431.png

 

 

ara_4_1-1596894333415.png

 

Anonymous
Not applicable

HI @Fowmy 

 

Using the measure, the total istill calculates total of everything. I need only totals as totals of Actuals

 

As per my future state diagram

 

ara_4_0-1596894142181.png

My grand totals are 8.0, 99.4 ..  But in the screenshot you had provided, it calculates total of everything.

 

ara

Hi @Anonymous ,

 

Please refer to my .pbix file.

v-lionel-msft_0-1597027714082.png

 

 

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.

v-lionel-msft_1-1597028010290.png

 

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.

Hi @Anonymous ,

 

Has your problem been solved?

 

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.