Creating Measures

[jbezos]

Hi, First time using Power BI.  I'm trying to calculate the % distribution based on the values found in a column.  For example, I have a column of Fruits with Apples and Bananas as values.  I am trying to calulate the % highlighted in yellow.

THanks!

[v-sihou-msft · Accepted solution]

@jbezos

As @Phil_Seamark said, it's better to pivot your dataset like:

You can use a matrix to render your data. Then you just need to create a measure to calculate the percentage of month total for current fruit.

Pct Of Month Total = SUM(Fruits[Volume])/CALCULATE(SUM(Fruits[Volume]),ALLEXCEPT(Fruits,Fruits[Month]))

Regards,

[Phil_Seamark]

Hi @jbezos

I suggest you pivot your data to the following structure.

Month    , Fruit  , Value
------------------------------
1 Jan 17 , Apples , 1
1 Feb 17 , Apples , 5
1 Mar 17 , Apples , 15
1 Jan 17 , Bananas, 4
1 Feb 17 , Bananas, 10
1 Feb 17 , Bananas, 20

Once you have this structure you can create some measures like

Sum of Value = CALCULATE(SUM([Value]))

which you can drag to a matrix visual (Month on Rows, Fruit on Columns), twice.  The second time you add the measure to the grid, set the "show as percentage"

I think that will be close.

You can change the structure of your data in the Query Editor using the Unpivot function.