cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
cbb4
Frequent Visitor

Matrix table multiple data types in same column

I'm trying to get a matrix table into the format below where my rows are my metrics and I have two column hierarchies: Time and Current/Prior. For each metric I want to have the raw values for "Current" and then a %increase/decrease in the "Prior" column.

cbb4_0-1622665292421.png

 

This data is coming via DirectQuery from SQL and it looks like this: 

cbb4_1-1622665292434.png

 

I'm doing the % calculation in the SQL query, but if it's easier to create a measure to do that work then I can pull in the raw values. 

Ideally what I need is the ability to have the Current values be formatted as currency and the prior values formatted as a percent. Unfortunately I can't use the FORMAT() function with direct query so that's off the table. Is there any other way to achieve this?

1 ACCEPTED SOLUTION
cbb4
Frequent Visitor

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

 

 

cbb4_1-1622734136941.png

 

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?

View solution in original post

2 REPLIES 2
cbb4
Frequent Visitor

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

 

 

cbb4_1-1622734136941.png

 

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?

View solution in original post

DataInsights
Super User II
Super User II

@cbb4,

 

You might try using a calculation group. Each calculation item within a calculation group has a format property.

 

https://www.sqlbi.com/articles/controlling-format-strings-in-calculation-groups/ 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Top Solution Authors
Top Kudoed Authors