locked
Complexe percentage calculation RRS feed

  • Question

  • hi,

    I would like to compute percentage based on this table between ticket and CA based on BUV and ID parameters:

    ID BUV Ticket CA
    103 BUV31 2 274 € 32 662 €
    103 BUV2 1 979 € 32 662 €
    103 BUV35 1 788 € 32 662 €
    104 BUV31 2 492 € 40 900 €
    104 BUV2 2 311 € 40 900 €
    104 BUV14 2 283 € 40 900 €

    with a power pivot table we have that:
    Étiquettes de lignes CA Ticket pct
    103 97985,37 6042,14 6,17%
    BUV2 32661,79 1979,24 6,06%
    BUV31 32661,79 2274,44 6,96%
    BUV35 32661,79 1788,46 5,48%
    104 122699,16 7085,82 5,77%
    BUV14 40899,72 2283,21 5,58%
    BUV2 40899,72 2310,67 5,65%
    BUV31 40899,72 2491,94 6,09%
    Total général 110342,265 13127,96 11,90%

    where foor CA I created a DAX formulas in calculate Field:

    sum([CATotal])/DISTINCTCOUNT([OPFSessID])

    and for Ticket I created in calculate Field again:

    [SommeTicketTotal]

    And the result of the percentage field is still a Calculate Field which is the result of CA/Ticket comuted previously.

    It works perfectly if I want to display only one ID. But if two ID are displayed, the computation is the sum of percentage which is of course wrong.

    For instance if I think about BUV31, i should have 6.47% instead of 12.96%. How can I add buv parameter into the formula to get the right result?

    



    • Edited by mi jo Wednesday, November 12, 2014 1:17 PM add details about calculate fields
    Wednesday, November 12, 2014 10:40 AM

Answers

  • Hi mi jo,

    Ah, thanks for that clarification. I now understand the issue. We need to force the CA Calculated Field to be computed at the' right level (i.e. grain). In addition to the Calculated Fields that I listed in my last reply, I have now added this one:

    CA Calculated Field ID Grain:=SUMX(
      ADDCOLUMNS(
        VALUES(TableName[ID]),
        "TableName[CA Total]", [CA Calculated Field]
      ),
      TableName[CA Total]
    )

    I have also updated the Percentage Calculated Field to:

    Percentage Calculated Field:=
    DIVIDE(
      [Ticket Calculated Field],
      [CA Calculated Field ID Grain]
    )


    Hope this helps :)


    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
    Blog: http://www.nimblelearn.com/blog
    Twitter: @nimblelearn

    • Edited by Michael Amadi Thursday, November 13, 2014 10:02 AM Minor revision to DAX
    • Marked as answer by mi jo Thursday, November 13, 2014 10:22 AM
    Thursday, November 13, 2014 10:00 AM

All replies

  • Hi mi jo,

    Has your percentage calculation been used to define a Calculated Column or a Calculated Field. Please see How to ask a Power Pivot Question to get a prompt, accurate and helpful response and provide as much of what is described as possible so that we can understand your problem more clearly and propose an appropriate solution.


    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
    Blog: http://www.nimblelearn.com/blog
    Twitter: @nimblelearn

    Wednesday, November 12, 2014 11:49 AM
  • Any Ideas about my problem ?
    Wednesday, November 12, 2014 3:15 PM
  • Hi mi jo,

    Logically, the results don't look wrong. Could you perhaps clarify further why they aren't what you're expecting and perhaps walk through an example of how the correct value should be calculated? I'm unable to see the 12.96% value that you refer to above and the results don't appear to be a sum of percentages. In addition this, it appears that the final Calculated Field (i.e. percentage) is defined as [Ticket Calculated Field]/[CA Calculated Field] as opposed to the other way round based on the results in your screenshot.

    Below is my current understanding of the Calculated Fields based on your description.

    CA Calculated Field:=SUM(TableName[CA])/DISTINCTCOUNT(TableName[ID])

    Ticket Calculated Field:=SUM(TableName[Ticket])

    Percentage Calculated Field:=[Ticket Calculated Field]/[CA Calculated Field]

    Is this understanding correct?


    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
    Blog: http://www.nimblelearn.com/blog
    Twitter: @nimblelearn

    Wednesday, November 12, 2014 5:06 PM
  • Hi,

    Yes it is correct. I created a table with only BUV elements and theire percentage. 

    Here is the table:

    Étiquettes de lignes pct
    BUV14 5,58%
    BUV2 11,66%
    BUV31 12,96%
    BUV35 5,48%
    Total général 11,90%

    As you can see BUV31 has 12.96% but it is wrong it should appear ticket (2274.44+2491.94) / CA (32661.79+40889.72) = 6.48%. So, 0.1296*32661.79=4233. It use 4233 as ticket values. No idea about where it come from.

    I think I should add into the CA calculate field something like "group by" BUV. 

    Thursday, November 13, 2014 8:33 AM
  • Hi mi jo,

    Ah, thanks for that clarification. I now understand the issue. We need to force the CA Calculated Field to be computed at the' right level (i.e. grain). In addition to the Calculated Fields that I listed in my last reply, I have now added this one:

    CA Calculated Field ID Grain:=SUMX(
      ADDCOLUMNS(
        VALUES(TableName[ID]),
        "TableName[CA Total]", [CA Calculated Field]
      ),
      TableName[CA Total]
    )

    I have also updated the Percentage Calculated Field to:

    Percentage Calculated Field:=
    DIVIDE(
      [Ticket Calculated Field],
      [CA Calculated Field ID Grain]
    )


    Hope this helps :)


    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
    Blog: http://www.nimblelearn.com/blog
    Twitter: @nimblelearn

    • Edited by Michael Amadi Thursday, November 13, 2014 10:02 AM Minor revision to DAX
    • Marked as answer by mi jo Thursday, November 13, 2014 10:22 AM
    Thursday, November 13, 2014 10:00 AM
  • Yes it works perfectly. Thanks a lot :)
    Thursday, November 13, 2014 10:21 AM