Forum Discussion

edg's avatar
edg
New Member
10 years ago

Different data types for rows in Matrix Table (after unpivoting)

  Hello! So I have jsut started to figure out PowerBI Desktop and faced some problems. Unofortunately I couldn't find how do this so I hope someone here can help me.  I created Matrix table with th...
Need Help
Show and Tell
j7r8's avatar
j7r8
Regular Visitor
5 years ago

I am having a very similar issue, and working with a similar sort of data set. I am working with Fallout / Completion Counts and Rates for various web services, and trying to display it as a time-series bar / line chart with a data table underneath that shows, How many people started and completed the action (Sum), and the completion rate percentage (Average) each Month. My original table had a table with the following attributes: Transaction, Date, Start, Complete, Rate. I order to accomplish this I unpivoted that table so I have Transaction, Date, Attribute, and Value.The rows where Attribute = Rate are Percents. The rest are whole numbers. The chart looks great except for not displaying rate as a percent and summing rate instead of averaging it. I am a n00b, totally open to suggestions. Thank you! Examples of what I'm using and trying to do below:

 

Original Data TableUnpivoted the data columnsTable that goes along with time series bar chart

j7r8's avatar
j7r8
Regular Visitor
5 years ago

Okay, I think I figured it out. At least for me, this is what worked (Credit goes to Sabrina Zenatti's Matrix Formatting Article😞

After I un-pivoted my original table, I created a formatted column in the unpivoted data table:

 
FormattedValue =
IF ( FlowsDataTable_Flat[Attribute] = "Rate", FORMAT(FlowsDataTable_Flat[Value],"#%"), FORMAT(FlowsDataTable_Flat[Value],"###,###"))
 
After that I just updated the Matrix table to reference this new column instead of the previous column and I'm in business! I hope this helps 😁