Different Date Types for same Column

[Pammy2411]

Hi,

I am new to power bi so apologies if this is an obvious question.

I have a column called KPI and a different column with Values in it. However, depending on the KPI, different data types should be used (Integers or percentages) when displayed on, for example, a matrix.

Is it possible to do this in Power BI?

Thanks in advance,

Pamela

[hectorv1]

It is possible to show different formats in the same column for a TableMatrix as shown below.  In a Matrix use the formatting Values option "Show in Rows".

 

This post show a full sample from the picture:

http://hectorv.com/table-or-matrix-for-side-by-side-column-comparison-with-columns-with-different-fo...

[Anonymous]

@hectorv1  can we use all those rows (units, profit..) on a single filter / slicer? 

[AsadHamdani]

Hi,

You can very easily create a New Column, and use the IF formula to conditionally format each Row's value based on the values in your KPI column.

So, if e.g. your Table's name is Table1, and it has two columns "KPI", and "Figure", where KPI column contains labels such as "Hits", "Conversion", and you want to convert each Conversion figure as % but all other as Integers or a Standard Number, you can use the following formula in a New Column to conditionally format each row:

New Column = IF(Table1[KPI]="Conversion",FORMAT(Table1[Figure],"Percent"),FORMAT(Table1[Figure],"Standard"))

For more FORMAT options, you can visit these links:

https://technet.microsoft.com/en-us/library/ee634561(v=sql.105).aspx

https://technet.microsoft.com/en-us/library/ee634206(v=sql.105).aspx

Hope this helps.

Asad Hamdani

[Marghe]

Hi,

thanks so much for this. I tried and it worked really well, the only problem I have is that one I try to use the new column in a graph all values turn to one. Is there a way of creating a column with two data types and have it maintain the values in a graphs?

Many thanks,

Marghe

[Anonymous]

@Pammy2411 check if this help:

https://community.powerbi.com/t5/Desktop/Switching-Values-on-a-report-with-a-splicer/td-p/73389

[augustindelaf]

@Pammy2411, please change the name to "Different Data Types for same Column", it is more understandable 🙂

thx

[MattAllington]

A column must have a single data type. Depending on what you are doing, there are 2 approaches

1. Put all data in a single column and write individual measures for each Kpi and apply appropriate formatting. The downside is you can slice on the Kpi label to change the Kpi. 

2.  Create multiple columns, one for each data type. Write a changing measure for each one. 

You our can vote for the ability to change formatting on a measure here. 

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/15231165-conditional-formatted-me...

[augustindelaf]

Hello @MattAllington,

I have exactly the same Problem. 

this issue is very common, I guess.

Technically , I can't manage to create multipe columns, all I need to do is to create these measure you are talking about.

Do you know the Syntax, and the name of the Measures, please?

(for me, my data in the single column should be displayed as euros, %, or integers, if the value of another column (named "KPI") is "euro" , "percent", or "integer".)

The visual I am using is also a Table; or a Matrix.

@If the PBI Product Team could quickly create a visual where you can easily manage the data format of a same field, it would be great.

Thanks

[Pammy2411]

Hi Matt,

Thank you very much for the response and i will most definitely vote for that idea, as it is a must have feature for such a powerful tool.

I read your workarounds, however I am still a bit confused as to how I can manage to display my data that require different kinds of Data Types.

An example, would be: on a Matrix - Row: Engine, KPI; Column: Month, Week ; Filter: Month - January and February only.

In KPIs there is currency and percentage data.

What would be the best way to structure the data?

Thank you,

Pamela

[MattAllington]

You can't do it. 😞

all  you can do is write different measures and put them on columns, and dates on rows. Or create 2 stand alone visuals/matrix. That's it

[Pammy2411]

That is unfortunate 😞

Many features from Pivot tables in Excel seem to be lacking in Power BI. It is so frustrating!

Thanks for your help though!

[v-haibl-msft]

@Pammy2411

It seems that this function is desired by many Power BI users. I’ve reported this requirement internally. Hope it can help raise the priority.

Best Regards,

Herbert

[Pammy2411]

Thats great to hear @v-haibl-msft.

Hopefully we can get a solution for this ASAP 🙂

Thank you,

Pamela