Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
Solved! Go to 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")
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")
Hi @win_anthony ,
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
@v-jayw-msft 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")
@v-jayw-msft 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?