Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
104 | |
78 | |
68 | |
61 |
User | Count |
---|---|
144 | |
106 | |
106 | |
82 | |
70 |