Data Export to Excel Not Correct

[Anonymous]

The goal is to export data which has a ratio. Problem is that the visual will correctly display the ratio but when exported, excel will generate the value as a date. The measure creating this ratio was formatted to be used as a KPI. The column's format for the exported data = "Custom". I tried playing around with the format by changing it to "General / Number / Text" but that did not resolve the issue. For context, below you will find the following: Ratio Measure /  Current Output / Expected Output

Not sure on how to remedy this but your advice is greatly appreciated.

Ratio Measure

sold_inventory_ratio =
IF( 
    TRUE()
    ,FORMAT([item_sold],"0,0") & " / " & FORMAT([item_inventory],"0,0")
    // ,FORMAT(VALUE([item_sold]),"0,0") & " / " & FORMAT(VALUE([item_inventory]),"0,0")
    // -- I also tried the above commented measure with FORMAT + VALUE but it was giving me the same output when exporting this ratio
    , 1
)

Current Output: Notice column [sold_inventory_ratio] >> Some values are ratios but some are listed as dates which is not correct

item_id	item	item_sold	item_inventory	sold_inventory_ratio
1	Pizza	15	54	15 / 54
2	Burgers	5	48	May-48
3	Tacos	12	46	Dec-46
4	Sushi	1	45	Jan-45
5	Salad	31	51	31 / 51

Expected Output: Notice column [sold_inventory_ratio] >> The ratios are correctly displayed

item_id	item	item_sold	item_inventory	sold_inventory_ratio
1	Pizza	15	54	15 / 54
2	Burgers	5	48	05 / 48
3	Tacos	12	46	12 / 46
4	Sushi	1	45	01 / 45
5	Salad	31	51	31 / 51

[Anonymous · Accepted solution]

I was able to find a resolution by updating the measure from " / " to " of "

sold_inventory_ratio =
FORMAT(VALUE([item_sold]),"0,0") & " of " & FORMAT(VALUE([item_inventory]),"0,0")

[Anonymous]

Hi @Anonymous ,

What is the Data Type of this column in Power BI Desktop?

Could you try changing it to Text Type then exporting again?

Best Regards,

Jay

[Anonymous]

@Anonymous I updated the measure and the data type is now Text. Unfortunately, I am still getting the same result for the data export. The export will result in some values reflected as Dates (just like in the example listed above). 

Any advice on how I can remedy this? Your advice is greatly appreciated. 

sold_inventory_ratio =
FORMAT(VALUE([item_sold]),"0,0") & " / " & FORMAT(VALUE([item_inventory]),"0,0")

[Anonymous]

@Anonymous the data type in Power BI Desktop for this measure was General. I tried to update it to Text but for some reason the data type is not changing. Any advice on how I can change the measure data type to Text?