Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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?
Need Help
  • v-lionel-msft's avatar
    v-lionel-msft
    6 years ago

    Hi Anonymous ,

     

    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.

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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

 

 

 

Fowmy's avatar
Fowmy
Super User
6 years ago

Anonymous 

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 🙂

YouTube, LinkedIn

 




  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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

     

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

     

    ara

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

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

     

     

     

     

  • v-lionel-msft's avatar
    v-lionel-msft
    Community Support
    6 years ago

    Hi Anonymous ,

     

    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.

  • v-lionel-msft's avatar
    v-lionel-msft
    Community Support
    6 years ago

    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.