percentage calculation

[petrk]

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

date	name	value	per 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

[Anonymous · Accepted solution]

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

[collinq]

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]

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]

@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

[petrk]

hi @Kedar_Pande ,
calculation of Total_X is OK but Per_of_X has issue. PBI doesn't allow me enter Table[value] to formula. It offer me to use just another measures which I have calcalated.

PBI error: A single value for column 'value' in table 'myTable' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

Do you have some idea how to solve it?

[Kedar_Pande]

Updated Measure:

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

[petrk]

@Kedar_Pande
now formula works, but it returns value just for rows with x1, x2, x3. see picture