0

My issue is similar to this one Multiple data types in a Power BI matrix but I've got a bit of a different setup that's throwing everything off.

What I'm trying to do is create a matrix table with several metrics that are categorized as Current (raw data values) and Prior (year over year percent growth/decline). I've created some dummy data in Excel to get the format the way I want it in PowerBI (see below):

Desired Format

As you can see the Current values are coming in as integers and the Prior % numbers as percentages which is exactly what I want; however, I was able to accomplish this through a custom column with the following formula:

Revenue2 = IF(Scorecard2[Current_Prior] = "Current", FORMAT(FIXED(Scorecard2[Revenue],0), "$#,###"), FORMAT(Scorecard2[Revenue], "Percent"))

The problem is that the data comes from a SQL query and you can't use the FORMAT() function in DirectQuery. Is there a way I can have two different datatypes in the same column of data? See below for how the SQL data comes into PowerBI (I can change this if need be):

SQL

CBags4
  • 1
  • 1

2 Answers2

0

Create 2 separate measures, one for the Current second for Prior, and format these measures. Probably you can also use a case in SQL query to format your data to bring it as STRING.

msta42a
  • 3,601
  • 1
  • 4
  • 14
  • If I do this then I will have two Revenue metrics appear in the rows. I need it to just be one. – CBags4 Jun 02 '21 at 14:14
  • IF you want only one and you dont want to aggregate this column then format whole column in sql using cast and case (and return as string) – msta42a Jun 03 '21 at 11:46
0

What I wound up doing was reformatting the SQL code to look like this:

Solution

That way Current/Prior are have two separate values and the "metric" is categorical.

I got the idea from this post: Simple way to transpose columns and rows in SQL?

CBags4
  • 1
  • 1