new table from mix of measures from other table and formulas

[Anonymous]

HELP!!!

This is what I want to create, a table that has a column of lines for a P&L and their corresponding values.

I have a fact table that has some of the "line items" and corresponding values, but need to add in the line items for GP, GP%, Totals, EBIT, etc...

I thought this formula would do it but it gives me an error, "A table of multiple values was supplied where a single value was expected"  This is the formula I have"

$ = SWITCH(values('Fact Table'[Line Item]),

"Product Sales", [Amount (YTD)]*-1,

"Labour Sales", [Amount (YTD)]*-1,

"Total Sales", calculate([Amount (YTD)]*-1, filter('Fact Table', or('Fact Table'[Line Item] = "Product Sales", 'Fact Table'[Line Item] = "Labour Sales"))),

"Product GP", CALCULATE([Amount (YTD)]*-1, filter('Fact Table', 'Fact Table'[Line Item] = "Product Sales")) - calculate([Amount (YTD)]*-1, filter('Fact Table', 'Fact Table'[Line Item] = "Product Cost")),

"Total GP", calculate([Amount (YTD)]*-1, filter('Fact Table', 'Fact Table'[Line Item] = "Total Sales")) - CALCULATE([Amount (YTD)]*-1, filter('Fact Table', or('Fact Table'[Line Item] = "Product Cost", 'Fact Table'[Line Item] = "Labour Cost"))),

"Wages", CALCULATE([Amount (YTD)]*-1, filter('Fact Table', 'Fact Table'[Line Item] = "Labour Cost")),

"Property", [Amount (YTD)]*-1, "Vehicle", [Amount (YTD)]*-1,

"Other", [Amount (YTD)]*-1,

"Total Expenses", calculate([Amount (YTD)]*-1, FILTER('Fact Table', 'Fact Table'[Line Item] in {"Wages", "Property", "Vehicle", "Other"})),

"EBIT", calculate([Amount (YTD)]*-1, filter('Fact Table', or('Fact Table'[Line Item] = "Product Sales", 'Fact Table'[Line Item] = "Labour Sales"))) - calculate([Amount (YTD)]*-1, FILTER('Fact Table', 'Fact Table'[Line Item] in {"Wages", "Property", "Vehicle", "Other"})))

HELP!!!  How do I make this work?!

[Greg_Deckler]

That's a lot of code. However, the issue is with the first line. Don't use VALUES, that returns a table and SWITCH is expecting a single value. Just get rid of the VALUES function. You may have other issues.

$ = SWITCH('Fact Table'[Line Item],
"Product Sales", [Amount (YTD)]*-1,
"Labour Sales", [Amount (YTD)]*-1,
"Total Sales", calculate([Amount (YTD)]*-1, filter('Fact Table', or('Fact Table'[Line Item] = "Product Sales", 'Fact Table'[Line Item] = "Labour Sales"))),
"Product GP", CALCULATE([Amount (YTD)]*-1, filter('Fact Table', 'Fact Table'[Line Item] = "Product Sales")) - calculate([Amount (YTD)]*-1, filter('Fact Table', 'Fact Table'[Line Item] = "Product Cost")),
"Total GP", calculate([Amount (YTD)]*-1, filter('Fact Table', 'Fact Table'[Line Item] = "Total Sales")) - CALCULATE([Amount (YTD)]*-1, filter('Fact Table', or('Fact Table'[Line Item] = "Product Cost", 'Fact Table'[Line Item] = "Labour Cost"))),
"Wages", CALCULATE([Amount (YTD)]*-1, filter('Fact Table', 'Fact Table'[Line Item] = "Labour Cost")),
"Property", [Amount (YTD)]*-1, "Vehicle", [Amount (YTD)]*-1,
"Other", [Amount (YTD)]*-1,
"Total Expenses", calculate([Amount (YTD)]*-1, FILTER('Fact Table', 'Fact Table'[Line Item] in {"Wages", "Property", "Vehicle", "Other"})),
"EBIT", calculate([Amount (YTD)]*-1, filter('Fact Table', or('Fact Table'[Line Item] = "Product Sales", 'Fact Table'[Line Item] = "Labour Sales"))) - calculate([Amount (YTD)]*-1, FILTER('Fact Table', 'Fact Table'[Line Item] in {"Wages", "Property", "Vehicle", "Other"})))

[v-frfei-msft]

Hi @Anonymous,

Based on my test, the issue should be related your measure. Could you please share your pbix to me ?

Column = SWITCH(Survey[department],"A",1,"B",2,"C", CALCULATE([total],FILTER(Survey,OR(Survey[department]="A",Survey[department]="B"))))

Regards,

Frank

[Anonymous]

Yup, a lot of formula!  

When I take out the values function at the beggining I get the error:

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