Forum Discussion

joshua1990's avatar
joshua1990
Icon for Post Prodigy rankPost Prodigy
1 year ago
Solved

Different format for measure per row

I have a matrix that shows different measures for individual groups.

The groups are segmented on the rows:

GroupMeasure 1Measure 2
A1K3K
B500300
C2K3K
Total3.5K6.3K

 

Is it possible to define the format for a measure based on the selected group? As you can see the K format is only applicable for Group A and C.

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi joshua1990 

    You can refer to the following solution.

    1.Create a new table, there is no relationship between the tables.

    Table 2 = var a=VALUES('Table'[Group])
var b={"Total"}
return UNION(a,b)

    2.Create the following measures.

    Measure3 =
IF (
    SELECTEDVALUE ( 'Table 2'[Group] ) <> "Total",
    CALCULATE ( [Measure], 'Table'[Group] IN VALUES ( 'Table 2'[Group] ) ),
    SUMX ( ALLSELECTED ( 'Table'[Group] ), [Measure] )
)

    Measure4 =
IF (
    SELECTEDVALUE ( 'Table 2'[Group] ) <> "Total",
    CALCULATE ( [Measure 2], 'Table'[Group] IN VALUES ( 'Table 2'[Group] ) ),
    SUMX ( ALLSELECTED ( 'Table'[Group] ), [Measure 2] )
)

    Then change the format measure to the following.

    Format_measure = IF(SELECTEDVALUE('Table 2'[Group]) in {"A","C","Total"},"#,##0,.0 K")

    Then put the format measure to the format of measure3 and measure4.  and create a matrix visual ,and put the field of table(2) to the row ,and put the measure3 and measure4 to the value, and close the row subtotals.

     

     

    Output

     

    Best Regards!

    Yolo Zhu

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

     

6 Replies

  • FreemanZ's avatar
    FreemanZ
    Icon for Super User rankSuper User
    1 year ago

    hi joshua1990 ,

     

    try like:

    Measure 1b =

    IF(

        MAX(data[group]) IN {"A", "C"},

        [Measure 1]/1000 &" K",

        [Measure 1]

    )

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi,

    Thanks for the solution FarhanJeelani  and FreemanZ  offered, and i want to offer some more information for user to refer to.

    hello joshua1990, you can use dynamic format, you can create a new measure.

    Format_measure = IF(SELECTEDVALUE('Table'[Group]) in {"A","C"},"#,##0,.0 K")

    Then click the measure1 and measure2, select Measure tool->format->dynamic

     

    Then you can put the format measure to the related field.

    Output

    You can refer to the following link about the dynamic format.

    Create dynamic format strings for measures in Power BI Desktop - Power BI | Microsoft Learn

     

    Best Regards!

    Yolo Zhu

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

    • joshua1990's avatar
      joshua1990
      Icon for Post Prodigy rankPost Prodigy
      1 year ago

      THanks a lot Anonymous.

      Is it possible to get the total in thousand as well?

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        Hi joshua1990 

        You can refer to the following solution.

        1.Create a new table, there is no relationship between the tables.

        Table 2 = var a=VALUES('Table'[Group])
var b={"Total"}
return UNION(a,b)

        2.Create the following measures.

        Measure3 =
IF (
    SELECTEDVALUE ( 'Table 2'[Group] ) <> "Total",
    CALCULATE ( [Measure], 'Table'[Group] IN VALUES ( 'Table 2'[Group] ) ),
    SUMX ( ALLSELECTED ( 'Table'[Group] ), [Measure] )
)

        Measure4 =
IF (
    SELECTEDVALUE ( 'Table 2'[Group] ) <> "Total",
    CALCULATE ( [Measure 2], 'Table'[Group] IN VALUES ( 'Table 2'[Group] ) ),
    SUMX ( ALLSELECTED ( 'Table'[Group] ), [Measure 2] )
)

        Then change the format measure to the following.

        Format_measure = IF(SELECTEDVALUE('Table 2'[Group]) in {"A","C","Total"},"#,##0,.0 K")

        Then put the format measure to the format of measure3 and measure4.  and create a matrix visual ,and put the field of table(2) to the row ,and put the measure3 and measure4 to the value, and close the row subtotals.

         

         

        Output

         

        Best Regards!

        Yolo Zhu

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

         

  • FarhanJeelani's avatar
    FarhanJeelani
    Icon for Super User rankSuper User
    1 year ago

    In Power BI, you can create different formats for measures based on the group by using a conditional formatting approach in DAX. Here’s how you can accomplish this:

    1. Define the Custom Formatting in DAX: Create a DAX measure that formats the values conditionally based on the group.

    2. Use SWITCH for Conditional Formatting: You can use `SWITCH` along with `FORMAT` to apply different formats depending on the group.

    Here’s an example DAX measure to achieve this:

    DAX
    Formatted Measure =
    SWITCH(
    TRUE(),
    'YourTable'[Group] = "A", FORMAT([Measure], "0K"),
    'YourTable'[Group] = "C", FORMAT([Measure], "0K"),
    FORMAT([Measure], "0")
    )

    In this example:
    - For Group A and C, the measure will display in "K" format (thousands).
    - For other groups, it will use the default format.

    ### Important Note
    This approach works well for creating text-based formats in a matrix visual. However, because `FORMAT` converts numbers to text, this might affect sorting and aggregations, as Power BI treats them as text values.

    If you need both numeric sorting and dynamic formatting, another approach would involve creating separate columns or measures and applying formatting in the matrix visual directly, but this is often more complex.

    Let me know if you'd like further customization!