Turning Grams to Quantity

[courtnielingaas]

Hi! I'm very new to pbi and I'm trying to define a measure that will take the quantity in grams (formatted this way in source) and calculate quantity based off our flower category units like 28g, 3.5g, 2.5g, etc. based on current stock. I know I need to define a measure that will do the calculations "Quantity in Oz = [Grams] / 28.35" and then one with a distinct count to actually create the action of the conversion. Can anyone provide me with a little more guidance?

[v-dineshya · Accepted solution]

Hi @courtnielingaas ,

Thank you for reaching out to the Microsoft Community Forum.

Step 1: Create a Measure for Quantity in a Specific Unit Size

To calculate how many 3.5g units you can get from the [Grams]:

Units_3_5g = SUM('YourTable'[Grams]) / 3.5

Note: This gives you the total number of 3.5g units you could get from your grams across your dataset.

You can create similar measures for other unit sizes:

Units_28g = SUM('YourTable'[Grams]) / 28

Note: SUM('YourTable'[Grams]) ensures you’re totaling grams across rows. If each row already represents a total, you might use just [Grams].

Round Down to Whole Units: Usually, you don’t sell fractions of a unit (e.g., 0.8 of a 3.5g package). So use ROUNDDOWN to get full units:

Units_3_5g_Rounded = ROUNDDOWN(SUM('YourTable'[Grams]) / 3.5, 0)

Step 2: Create a Dynamic Measure Based on Selected Unit
If you want users to pick a unit size (e.g., from a slicer), you could set up a unit size table like:

Unit Name Grams
3.5g 3.5
28g 28
2.5g 2.5

Then use a measure like:

Units_Dynamic =
VAR SelectedUnitGrams = SELECTEDVALUE('UnitTable'[Grams])
RETURN
ROUNDDOWN(SUM('YourTable'[Grams]) / SelectedUnitGrams, 0)

Note: You'd add a slicer using 'UnitTable'[Unit Name], and the measure will auto-update.

If my response has resolved your query, please mark it as the Accepted Solution to assist others. Additionally, a 'Kudos' would be appreciated if you found my response helpful.

Thank you

[v-dineshya]

Hi @courtnielingaas ,

If my response has resolved your query, please mark it as the Accepted Solution to assist others. Additionally, a 'Kudos' would be appreciated if you found my response helpful.

Thank you

[v-dineshya]

Hi @courtnielingaas ,

If my response has resolved your query, please mark it as the Accepted Solution to assist others. Additionally, a 'Kudos' would be appreciated if you found my response helpful.

Thank you

[v-dineshya]

Hi @courtnielingaas ,

If my response has resolved your query, please mark it as the Accepted Solution to assist others. Additionally, a 'Kudos' would be appreciated if you found my response helpful.

Thank you