Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
diegoadum
Helper I
Helper I

Create a table with mixed format values

Hi Folks, I have to create a new table (Below the example) showing different KPI's and some of them are a percentage format and some others are a regular decimal number format. In the DB we have the values in the same column, so, I'm wondering how can we get this done?

 

Thanks in advance!

 

 

Periods

 

 

KPI

1

2

3

KPI 1

10

11

9

KPI 2

98%

97%

95%

KPI 3

9.5

6.8

7.8

4 REPLIES 4
samdthompson
Memorable Member
Memorable Member

The problem is that you can only apply one kind of formatting per column. The solution in this case would be to make a new KPI table:

 

1. Ribbon/Modelling/New_Table and enter the dax which will be something like this: SUMMARIZE(TABLE, KPI_NAME, KPI_VALUE, KPI_DATE)

 

2. Open in query editor and unpivot making the KPI values column headers Ribbon/Transform/Unpivot

 

3. Apply and create relatioinships:

       Date

       KPI name

 

4. Use Names in the KPI table as the row headers and the new value columns as the measures and the date table as the column headers

 

Longterm, maybe it would be good to let the KPI's be calculated in powerbi.

 

 

 

// If this is a solution please mark as such

 

// if this is a solution please mark as such. Kudos always appreciated.

Hi, thanks for your suggestion, but I'm a little lost following your step 2. How can I go to the query editor if the new table was created outside the Query Editor?

 

Thank in advance!

@diegoadum

 

It's not possible to configure dynamic format for a single column. And if you create a calculated column with FORMAT(), the data type will be text. For your requirement, it can't be achieved unless you unpoivt KPI2 into a column as @samdthompson suggested.

 

Regards,

@diegoadum Oh man, my mistake. Sorry about that. Two solutions:

 

1. use something like KPI _TABLE = SUMMARIZE(TABLE, KPI_NAME, KPI_DATE)

create the relationship for the KPI name and for the date

then bring in calculated columns for each of the KPI's you want. Assuming that there is just one kpi per name per date then you might use:

 

KPI1 = CALCULATE(AVERAGE(TABLE[KPI_VALUE]),KPI[NAME]="KPI1")

KPI2 = CALCULATE(AVERAGE(TABLE[KPI_VALUE]),KPI[NAME]="KPI2")

KPI3 = CALCULATE(AVERAGE(TABLE[KPI_VALUE]),KPI[NAME]="KPI3")

...

 

or

 

2. Take the existing table in the query editor and duplicate it, remove any unessesary columns, then do the unpivoting.

 

With both solutions you need to format to what ever you want. Remeber the row headers will be the KPI name column from the new table.

 

 

 

// If this is a solution please mark as such

// if this is a solution please mark as such. Kudos always appreciated.

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors