Help with calculated field (PBI Desktop)

• Question

• Hello,

I have several records pertaining to opportunities and each record has a column named Total Amount and a state.

Now, I imported my query to PBI Desktop and I can't seem to find a way to make a calculation based on those 2 columns.

I added a visualization that shows Total Amount where the state is Won.

I also added a visualization that shows the Total Amount without a state filter.

Now I need another visualization that gives me Total Amount(Won)/ Total Amount but I can't seem to find a way to make this happen.

Can someone offer some insight please?

Thanks

Friday, December 4, 2015 4:32 PM

• Hi Agenteusa. It sounds like you may want to create a measure that calculates Amt Won/Total Amt.

https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-tutorial-create-measures/

Ehren

Friday, December 4, 2015 9:50 PM
• Hi Agenteusa,

Adding to what Ehren has mentioned, for your specific scenario you can create measures for the total amount, total amount for all state opportunities, and then divide one by the other to get the percentage of the total.

You can use the above method (shown in the image) to create measures similar to the following:

`SumOfTotalAmount = SUM(Opportunities[Total Amount])`

`SumOfTotalAmountALL = CALCULATE([SumOfTotalAmount], ALL(Opportunities[State]) )`

`PercentageOfTotalAmount = DIVIDE([SumOfTotalAmount], [SumOfTotalAmountALL])`

You can then use your 'PercentageOfTotalAmount' measure to create your third chart:

It's recommend that you post future questions regarding the Power BI Desktop application, or the Service, on the official Power BI Community forums: http://community.powerbi.com/.

Regards,

Monday, December 21, 2015 7:16 AM

All replies

• Hi Agenteusa. It sounds like you may want to create a measure that calculates Amt Won/Total Amt.

https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-tutorial-create-measures/

Ehren

Friday, December 4, 2015 9:50 PM
• Hi Agenteusa,

Adding to what Ehren has mentioned, for your specific scenario you can create measures for the total amount, total amount for all state opportunities, and then divide one by the other to get the percentage of the total.

You can use the above method (shown in the image) to create measures similar to the following:

`SumOfTotalAmount = SUM(Opportunities[Total Amount])`

`SumOfTotalAmountALL = CALCULATE([SumOfTotalAmount], ALL(Opportunities[State]) )`

`PercentageOfTotalAmount = DIVIDE([SumOfTotalAmount], [SumOfTotalAmountALL])`

You can then use your 'PercentageOfTotalAmount' measure to create your third chart:

It's recommend that you post future questions regarding the Power BI Desktop application, or the Service, on the official Power BI Community forums: http://community.powerbi.com/.

Regards,