Create a measure from Column Values

[HaroldqPants]

Hi Forum,
I have some data in an odd format, most columns are what you would expect, but one Column 'KPIs' contains the Values 'Units' & 'Sales'.
There's also measures in another Column 'Actuals' for the 'Units' & 'Sales' figures.
I can transpose the  'KPIs' Column and get the 'Actuals' for 'Units' & 'Sales', the problem is that I want to create a new Column from this transposed data to do a calculation like 'Sales' divided by 'Units'.
Any suggestions welcome.
Thanks

ProductKPIsActuals

Product1	Units	231
Product1	Sales	1346
Product2	Units	245
Product2	Sales	3426
Product3	Units	546
Product3	Sales	45278
Product4	Units	378
Product4	Sales	7642
Product5	Units	299
Product5	Sales	84332

[rajendraongole1 · Accepted solution]

Hi @HaroldqPants - In power query editor

Go to the Transform tab, and click on Pivot Column.
In the Pivot Column dialog, select 'KPIs' as the column to pivot, and 'Actuals' as the values column.
For the Advanced options, choose Don't Aggregate.

Reference:

Go to the Add Column tab and click on Custom Column.

Click close & Apply to return to the Power BI report view.

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

[Anonymous · Accepted solution]

Hi @HaroldqPants ,

@rajendraongole1 Good Answer!
And @HaroldqPants Not all data sources can use Power Query, it depends on your connection mode. Only in Import mode can you fully use Power Query. If you are in Direct Query mode, your Power Query will be limited, some functions cannot be used, and some data is not visible to you. If you are in Live Connection mode, you cannot use Power Query. In addition, if the data table is a calculated column or a calculated table, it is not visible in Power Query.
I can provide you with a method using DAX to achieve your needs.
Use the following DAX to create a measure:

'Sales' divided by 'Units'_measure = 
VAR _CurrentProduct = MAX('Table'[Product])
VAR _Sales = 
CALCULATE(
    SUM('Table'[Actuals]),
    ALL('Table'),
    'Table'[Product] = _CurrentProduct && 'Table'[KPIs] = "Sales"
)
VAR _Units = 
CALCULATE(
    SUM('Table'[Actuals]),
    ALL('Table'),
    'Table'[Product] = _CurrentProduct && 'Table'[KPIs] = "Units"
)
RETURN
DIVIDE(_Sales, _Units)

Use the following DAX to create a calculated column:

'Sales' divided by 'Units' = 
VAR _CurrentProduct = 'Table'[Product]
VAR _Sales = 
CALCULATE(
    SUM('Table'[Actuals]),
    ALL('Table'),
    'Table'[Product] = _CurrentProduct && 'Table'[KPIs] = "Sales"
)
VAR _Units = 
CALCULATE(
    SUM('Table'[Actuals]),
    ALL('Table'),
    'Table'[Product] = _CurrentProduct && 'Table'[KPIs] = "Units"
)
RETURN
DIVIDE(_Sales, _Units)

And the final output is as below:

Best Regards,
Dino Tao
If this post helps, then please consider Accept both of the answers as the solution to help the other members find it more quickly.

[HaroldqPants]

Thanks Dino,

Apologies for the delayed response. I'll try this and come back to you.

Appreciate the effort you went to, thanks again.

[rajendraongole1 · Accepted solution]

Hi @HaroldqPants - In power query editor

Go to the Transform tab, and click on Pivot Column.
In the Pivot Column dialog, select 'KPIs' as the column to pivot, and 'Actuals' as the values column.
For the Advanced options, choose Don't Aggregate.

Reference:

Go to the Add Column tab and click on Custom Column.

Click close & Apply to return to the Power BI report view.

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

[HaroldqPants]

Thanks rajendraongole1,

I've got the example data in a Table in Power BI Desktop but when I go to Transform, the data isn't there?

Sorry to ask such a bsaic question but how do I see my table in the Transform pane? 

[rajendraongole1]

Hi @HaroldqPants - You have to extract the data from the table source system to Power BI and  then navigate the power query editor transform data. 

if you are already connected the data, check the permission are there with your name?

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

Spoiler