Error "this can happen when a measure formula refers to a column that contains many values"

[RN_87]

I'm trying create a financial statement and I'm new to PBI. I have a dimension table for my report layout that is connected to a fact table via a relationship at the line name. Some of data flowing into the rows need to be in percentage and other just numbers with commas. I created two measures sumActivity = SUM('fctMapping'[FlipActivity]), and PCTActivity = FORMAT([sumActivity],"0.0%").

. 

When I bring the measures into values they populate like this:

The highlighted row represents the row that I want to show up as a percentage. 

In the dimReportLayout table there is a column DataFormat that contains the identifier PCT for rows I want to show percentage, NUM for rows as numbers with commas, and NA for rows without any data. 

To bring the correct format into the same column I tried creating a new measure 

FormatSwitch = SWITCH(TRUE(),SELECTEDVALUE(dimReportLayout[DataFormat] = "PCT"),[PCTActivity],SELECTEDVALUE(dimReportLayout[DataFormat] = "NUM"),[sumActivity]) and I keep getting the following error:

A single value for column 'DataFormat' in table 'dimReportLayout' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result. 

Please help.

[amitchandak · Accepted solution]

@RN_87 ,Measures like

FormatSwitch = SWITCH(TRUE(),SELECTEDVALUE(dimReportLayout[DataFormat]) = "PCT",
[PCTActivity],SELECTEDVALUE(dimReportLayout[DataFormat]) = "NUM"
,[sumActivity])

Please use new dynamic String format , This article has good example for what you want to do

https://powerbi.microsoft.com/en-us/blog/deep-dive-into-the-new-dynamic-format-strings-for-measures/

[RN_87]

Thank you. That worked.