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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Pammy2411
Frequent Visitor

Different Date Types for same Column

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

13 REPLIES 13
hectorv1
Frequent Visitor

 

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".

 TableColumnComparison-Updated.png

 

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
Not applicable

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

AsadHamdani
Regular Visitor

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"))

 

Different Data Formats in Single Columns Power BI.PNG

 

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

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
Not applicable
augustindelaf
Impactful Individual
Impactful Individual

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

thx

MattAllington
Community Champion
Community Champion

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...



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
I will not give you bad advice, even if you unknowingly ask for it.

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

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

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



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
I will not give you bad advice, even if you unknowingly ask for it.

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!

@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

Thats great to hear @v-haibl-msft.

 

Hopefully we can get a solution for this ASAP 🙂

 

Thank you,

Pamela

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 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.

October NL Carousel

Fabric Community Update - October 2024

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