Forum Discussion

petrk's avatar
petrk
New Member
1 year ago
Solved

percentage calculation

Hi, please for help.
I have table like this, and I need calculate values in column "per of X", it is calculated by formula "per of X = value / X" where X is sum of all values which has in name "x". So X=5+4+3=12

At the report I have slicer for filtering dates. And I need to keep calculation according this filter. So use calculation for all visible values.  
Thank you for you ideas

 

datenamevalueper of X
01.03.2024  x1  5  42%
07.07.2024  x2  4  33%
07.09.2024  x3  3  25%
17.06.2024  y1  10  83%
25.06.2024  y2  8  67%
09.08.2024  u1  1  8%
11.09.2024  u2  5  42%
    
  X  12  
    

 

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi petrk ,

     

    I've made a test for your reference:

    1\ I assuem there is a table(Table)

    2\Add a measure

    Per of X = 
Var CurrentItemValue =Max('Table'[value])
Var CurrentItemName= LEFT(SELECTEDVALUE('Table'[name]),1)
Var SUMValue=CALCULATE(SUM('Table'[value]),FILTER(ALL('Table'),LEFT('Table'[name],1)=CurrentItemName))
RETURN
CurrentItemValue/SUMValue

    3\Result

     

    Best Regards,

    Bof

     

7 Replies

  • collinq's avatar
    collinq
    Super User
    1 year ago

    Hi petrk ,

     

    You may have better results in the Dax section for Power BI but I will take a run at this.  I belive that this is two measures.  The first one is to calculate the summation based on the Name colum.  So, it will be like this:
    SumValue = Calculate(Sum(Table[value]), Allexcept(Table, Table[name]))

     

    Then, the second measure will be to determine what percentage is each individual row against that total:
    % = DIVIDE(Table[Value], Table[TotalByName], 0)

     

    Give that a try!

    • petrk's avatar
      petrk
      New Member
      1 year ago

      Hi collinq ,

      thank you, but your solution doesn't work. I have attached pictures of report and your calculation. Wha I have wrong?   

       

       

      • Kedar_Pande's avatar
        Kedar_Pande
        Super User
        1 year ago

        petrk 

        Measure for Sum of X Values:

        Total_X =
        CALCULATE (
        SUM(Table[value]),
        FILTER (
        Table,
        LEFT(Table[name], 1) = "x" // Filters rows where name starts with 'x'
        )
        )

        Per of X Measure:

        Per_of_X =
        DIVIDE (
        Table[value],
        [Total_X],
        0 // To handle division by zero
        )

         

        If this helped, a Kudos 👍 or Solution mark would be great!
        Cheers,
        Kedar Pande
        www.linkedin.com/in/kedar-pande

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi petrk ,

     

    I've made a test for your reference:

    1\ I assuem there is a table(Table)

    2\Add a measure

    Per of X = 
Var CurrentItemValue =Max('Table'[value])
Var CurrentItemName= LEFT(SELECTEDVALUE('Table'[name]),1)
Var SUMValue=CALCULATE(SUM('Table'[value]),FILTER(ALL('Table'),LEFT('Table'[name],1)=CurrentItemName))
RETURN
CurrentItemValue/SUMValue

    3\Result

     

    Best Regards,

    Bof