Forum Discussion
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
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
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
Pammy2411, please change the name to "Different Data Types for same Column", it is more understandable :)
thx
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:
hectorv1 can we use all those rows (units, profit..) on a single filter / slicer?
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.
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
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
