none
Help with calculated field (PBI Desktop) RRS feed

  • 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

Answers

  • 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
    Owner
  • 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,

    Michael Amadi

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful :)

    Website: http://www.nimblelearn.com, Twitter: @nimblelearn

    Monday, December 21, 2015 7:16 AM
    Moderator

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
    Owner
  • 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,

    Michael Amadi

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful :)

    Website: http://www.nimblelearn.com, Twitter: @nimblelearn

    Monday, December 21, 2015 7:16 AM
    Moderator