Create a table with mixed format values

[diegoadum]

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

[samdthompson]

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

[diegoadum]

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!

[v-sihou-msft]

@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,

[samdthompson]

@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