DAX Format Statement breaks matrix or table hierarchy

[Anonymous]

I have a project to create a P&L statement for an Oil and Gas Company.  The P&L is generated from accounting entries that include a non-ragged hierarchy as follows.

• P&L Rollup Group 
• P&L Category 
• GL Account - GL Amount Value

Each of the hierarchy levels has an associated master table where I've added attributes such as sort sequence and display lable to control presentation.

Display values are tied to the GL Amount Value

The first few sections of the hierarchy are shown below.

• Volume (s/b formatted as "#,##0")
  • Oil Volume
    • Oil Sales Accrual (4020200)
    • Oil Sales Actual (4020100)
  • Gas Volume
• Revenue (s/b formatted as $#,##0)
  • Oil Revenue
  • Gas Revenue
• Price (s/b formatted as $#,##0.00)
  • Oil Price per unit
  • Gas Price per unit
• Production Costs (s/b formatted as $#,##0)
  • Production Cost - Oil
  • Production Cost - Gas

This report has been created using a Matrix to allow drill down thru the hierarcy with Display amounts pivoted by account month across the columns (Jan --> Dec). 

To address the dynamic formatting by PL Rollup Group I added a "format code" column to my master table for P&L Rollup Categories (Volume = "#,##0", Revenue = "$#,##0", Price = "$#,##0.00", etc...) and used the FORMAT function to dynamically apply the filter to the GL amount for display  based on the P&L Rollup Category for that row.

GL Amount Display = FORMAT(sum(GLDetail[GL Amount]), Min(PLRollupCategory[FormatCode])).

This application of the format codes works and correctly formats the displayed value based on the P&L rollup Category for the row. 

HOWEVER, when applying this format code , or even a hard coded format such as "#,##0", via DAX, the matix blows up and begins repeating all of the P&L Categories and GL accounts under each Rollup Category.  This means the Volume rollup section goes from 2 child catgories to 75 child categories and all of the 200+ accounts are repeated under each category.   

I tried switching the visual from Matrix to Table to see if the behavior was the same and saw the same results in a table visual as well.  Using a hardcoded Format value (GL Amount Display = FORMAT(sum(GLDetail[GL Amount]), "#,##0") produces the same results.

Can anyone tell me what I'm doing wrong?

Thx, Mike

[Anonymous]

Hi  @Anonymous 

did you get an answer about your issue? I am having the same problem, haven't found a solution 😞 

Thx

---

@Anonymous wrote:

I have a project to create a P&L statement for an Oil and Gas Company.  The P&L is generated from accounting entries that include a non-ragged hierarchy as follows.

• P&L Rollup Group 
• P&L Category 
• GL Account - GL Amount Value

Each of the hierarchy levels has an associated master table where I've added attributes such as sort sequence and display lable to control presentation.

Display values are tied to the GL Amount Value

The first few sections of the hierarchy are shown below.

• Volume (s/b formatted as "#,##0")
  • Oil Volume
    • Oil Sales Accrual (4020200)
    • Oil Sales Actual (4020100)
  • Gas Volume
• Revenue (s/b formatted as $#,##0)
  • Oil Revenue
  • Gas Revenue
• Price (s/b formatted as $#,##0.00)
  • Oil Price per unit
  • Gas Price per unit
• Production Costs (s/b formatted as $#,##0)
  • Production Cost - Oil
  • Production Cost - Gas

This report has been created using a Matrix to allow drill down thru the hierarcy with Display amounts pivoted by account month across the columns (Jan --> Dec). 

To address the dynamic formatting by PL Rollup Group I added a "format code" column to my master table for P&L Rollup Categories (Volume = "#,##0", Revenue = "$#,##0", Price = "$#,##0.00", etc...) and used the FORMAT function to dynamically apply the filter to the GL amount for display  based on the P&L Rollup Category for that row.

GL Amount Display = FORMAT(sum(GLDetail[GL Amount]), Min(PLRollupCategory[FormatCode])).

This application of the format codes works and correctly formats the displayed value based on the P&L rollup Category for the row. 

HOWEVER, when applying this format code , or even a hard coded format such as "#,##0", via DAX, the matix blows up and begins repeating all of the P&L Categories and GL accounts under each Rollup Category.  This means the Volume rollup section goes from 2 child catgories to 75 child categories and all of the 200+ accounts are repeated under each category.   

I tried switching the visual from Matrix to Table to see if the behavior was the same and saw the same results in a table visual as well.  Using a hardcoded Format value (GL Amount Display = FORMAT(sum(GLDetail[GL Amount]), "#,##0") produces the same results.

Can anyone tell me what I'm doing wrong?

Thx, Mike

---

[Anonymous · Accepted solution]

I finally got this to work by changing the filter direction on the 3 xref tables from single to "Both".  In retrospect, this kind of makes sense because

• Even before any formatting is applied, the Dax expression to calculate the Displayed Amount field depends on attributes of the PLCategoryCode when performing the calculation.  Note this calculation works with both filter direction options ("single" or "both")  regardless of whether or not a format is applied to the amount.
• In the final solution, the format expression (i.e. Format code) also lives on the PL Category table:  GL Amount Display = FORMAT(sum(GLDetail[GL Amount]), Min(PLRollupCategory[FormatCode])). 

Note that even applying a hardcoded format to the Displayed amount blew up the display when the filter direction was set to "Single".  I'm not sure why, but apparently this generated a query from CORDetail back to the CORPLCategoryCodes tables which was not supported by the original single relationship.   Setting the relationship to filter in both directions fixed the issue.

[Anonymous]

hi @Anonymous 

thanks for your solution and detailed explanation,

for my case I was using Format function inside and switch statement. while I was looking for a solution in web, I saw people, used switch statement in an IF statement.  It made sense then I tried to control any matrix cell value is blank or not with and IF statement, actually it worked too. Because dublicated columns has no value, after conrolling with and IF statement, dublicated column gone.

happy to solve the problem even if two different solutions.

have a nice week. 

[v-juanli-msft]

Hi @Anonymous 

Try to use a measure instead of a column with this formula

GL Amount Display = FORMAT(sum(GLDetail[GL Amount]), "#,##0")

If this doesn't help, please show me screenshots of your matrix or table and let me know a simple data structure.

Best Regards

Maggie

[Anonymous]

Maggie,

    Thanks for the reply.   I am using a calculated measure: GL Amount Display = FORMAT(sum(GLDetail[GL Amount]), Min(PLRollupCategory[FormatCode])).   However, even when I used a hard coded format code such as the one you showed, it still breaks the hierarchy and begins repeating children at each level.    This occurs whether I am using a matrix or a table.

In the images below, the version that is not formatted shows a nice 3 level hierarchy with 2 accounts as children of the Revenue Volume - Gas (mcf) category.   In the formatted version I've added a format code to the value displayed.  All children are repeated at each level, so the Revenue Volume - Gas (mcf) has 200 accounts under it instead of 2.

Thx, Mike

Not Formatted - 3 level hierarchyFormatted - duplicates all children in the hierarchyPartial Data Model