Formatting Numbers for the Total Row / First Row only

[stevensun]

Hi everyone,

I am working on finalizing a Power BI report and got stuck on some currency formatting issues.

I have some measures in place and built some matrix and tables, those measures are formatted to currency with the $ sign.

However, for the table and matrix I built, I need

1) The Dollar sign to only show on the first row and the first row might be a sub-total row

2) The Dollar sign to only show on a sub-total row, no matter where the sub-total row is in the table/matrix

I tried different ways and did my research but could not find anything helpful, I am wondering if any of you would have a better idea, thank you!

Example 1:

The below example is a screenshot of the matrix, you can see that it has two sub-total rows on the top, the first one is the total of all spend types, the second is the total of all categories under Type 1.

For both total rows, I need to keep the $ but for the rest of the numbers (Oct - 2021, Oct - 2020 and B(W) columns for Category 1-4 rows), I want to remove the $.

Example 2

In this example, the total rows are gone, so I need to keep the $ for the first row (category 2) and get rid of the $ for the 2nd and 3rd row.

[stevensun]

@Anonymous Thank you so much for looking into this, I have been playing around with the sample file you provided and applying what you did to my own file, so far, no luck on getting what I want.

I understand without taking a look at the dataset and visuals I have, it is very hard to answer my questions, I am working on cleaning and masking my data and would like to provide a sample of my real file here. If you can take a second look at it at that time, that would be much appreciated!!

I am also wondering if there is Microsoft Support available for this kind of issue, I have access to "Professional Direct" level support for Power Platform at work, I am wondering if this is something I can open a ticket with Microsoft under the "Advisory Support" feature?

[Anonymous]

Hi @stevensun ,

Yes, you can open a support ticket.

If this issue is urgent for you, suggest that you can consider creating a support ticket here for further help if you are a pro user.

Best Regards,

Stephen Tao

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

[Anonymous]

Hi @stevensun ,

I tried my best to achieve the effect you want to achieve. Both results are in text format, which requires attention.

Create two measures.

Measure = IF(ISINSCOPE('Table'[Category]),SUM('Table'[Value]),"$ "&SUM('Table'[Value]))

Measure 2 = IF(MAX('Table'[Category])="Category 2","$ "&SUM('Table'[Value]),SUM('Table'[Value]))

Reference: ISINSCOPE function (DAX) - DAX | Microsoft Docs

Best Regards,

Stephen Tao

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