Hi all,
I recently needed to have multiple formats in a single field where the totals and sub totals would calculate within a matrix, I couldn't find anything that matched exactly what I needed and after some trial and error I came up with the solution, so I thought i'd share in case someone else has the same issue.
In the table I had two quantity fields which needed to be whole numbers and three currency fields (Profit, Total Cost and Total Sales). The DAX Measure below uses CONTAINSSTRING & SELECTEDVALUE to search for the word ‘Profit’ or ‘Total’ within the Attribute and applies a format of currency to the Value field while retaining the ability to calculate the totals by using 'FORMAT & SUM', to all other fields it applies a whole number while again retaining the ability to calculate totals.
DAX Expression:
Value Measure For Multiple Data Formats = IF(OR(CONTAINSSTRING(SELECTEDVALUE(Table'[Attribute]),"Profit"),CONTAINSSTRING(SELECTEDVALUE('Table'[Attribute]),"Total")),FORMAT(SUM(Table'[Value])),"£#,##0.#0"),FORMAT(SUM('Table'[Value])),"#"))
If you're interested in why i needed to have multiple formats in one field check out my blog on the subject:
https://github.com/DOLEARY85/Power-BI-Replicate-Excel-Table
