- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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:
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@hectorv1 can we use all those rows (units, profit..) on a single filter / slicer?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@Pammy2411, please change the name to "Different Data Types for same Column", it is more understandable 🙂
thx
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
* 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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Microsoft Fabric Community Conference 2025
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
Subject | Author | Posted | |
---|---|---|---|
06-17-2024 06:42 AM | |||
07-18-2024 12:30 PM | |||
08-11-2024 04:10 AM | |||
03-22-2024 04:24 AM | |||
07-17-2024 03:26 AM |
User | Count |
---|---|
123 | |
80 | |
59 | |
58 | |
44 |
User | Count |
---|---|
182 | |
119 | |
82 | |
68 | |
53 |