Column with multiple data types

[icinine]

I have a KPI report that I receive every month in Excel.

I want to import it into Power BI, add come calculated columns and display it as a table / matrix with red/green up-down arrows, depending on the result of the calculation.

The problem is the report I receive is a mix of numeric and percentage data in the same column.

I would like to ask you experts whether the below is a suitable approach:

• Load the data into a staging table;
• Split the staging table into 2 dimensions - numeric KPI and percentage KPI;
• Perform any calculated column calculations on the 2 dimension tables
• Merge the dimension tables back into 1 report and convert the number to text for display purposes.

Thank you for reading.

[amitchandak]

@icinine ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[icinine]

Thanks for the reply.

The input is per below and the output is basically the same but with an added column comparing the current month with previous month.

Performance Activity / KPI Target/EA Full Year Target/EA YTD Activity YTD Jan Feb Mar Apr
Contracts Signed
New Contracts won 59,919 9,978 10,202 5,224 5,269 5,300 4,902
Widgets
Widgets sold 922,094 161,805 163,907 63,712 99,322 107,203 56,704
Order Fill rate
% of new orders processed within 12 weeks 81% 81% 76.7% 76.3% 80.0% 77.7% 75.5%
% of orders waiting less 6 months 94% 94% 72.9% 78.8% 77.9% 75.4% 72.9%
Total number of orders 587,604 100,495 62,981 40,545 32,978 32,338 30,643
Adverts
% of new adverts at development in less 12 weeks 71% 71% 64.8% 66.4% 71.8% 64.4% 65.3%
% of adverts in development less than 52 weeks 95% 95% 55.1% 59.1% 57.9% 56.5% 55.1%
No of adverts developed 3 89,256 65,931 50,217 30,300 25,460 24,768 25,449