topic Re: power bi DAX formula to calculate percentage from a column with multiple values in DAX Commands and Tips

power bi DAX formula to calculate percentage from a column with multiple values

Khushboo9966 — Wed, 19 Jun 2024 17:51:44 GMT

Hi,

I have two columns as per below table and I am trying to accomplish the below formula. Any pointers on how I can accomplish this?

A = Count of 1 = 2 | For A - 1 percentage = 2*10/1555 = 1%

A = Count of 2 = 0 | For B - 2 percentage = 0*13/1555 =

A = Count of 3 = 0 | For C - 3 percentage = 0 *15/1555 =

A = Count of 4 = 1

and similarly for B, C

Analyzed By	Value
A	1
B	2
C	1
A	1
B	3
C	5
A	4

Re: power bi DAX formula to calculate percentage from a column with multiple values

Khushboo9966 — Wed, 19 Jun 2024 18:33:37 GMT

I created a new formula to get the count based on Analyzed by and Value as follows.

_Count by Analyzed By by Value =

CALCULATE (

COUNTROWS ( 'Table' ) ,

ALLEXCEPT (

'Table' ,

'Table'[AnalyzedBy],

'Table'[Value]

)

I am mainly looking for finding percentages for each and I am using the following below. The formula works for 1 but for all other its does a cumulative percentage.

% = SWITCH( true(),

'Table'[Value] = 1, 'Table'[Value] *10/1555,

'Table'[Value] = 2, 'Table'[Value] *13/1555,

'Table'[Value] = 3, 'Table'[Value] *17/1555,

'Table'[Value] = 4, 'Table'[Value] *40/1555,

'Table'[Value] = 5, 'Table'[Value] *75/1555,

Re: power bi DAX formula to calculate percentage from a column with multiple values

Anonymous — Thu, 20 Jun 2024 02:37:57 GMT

Hi,Khushboo9966

Regarding the issue you raised, my solution is as follows:

1.First I have created the following table and the column names and data are the data you have given:

2.Create calculated column references:

Column = var rowcount=CALCULATE(COUNTROWS('Table'),ALLEXCEPT('Table','Table'[Analyzed By])) RETURN SWITCH( true(), rowcount = 1, 'Table'[Value] *10/1555, rowcount = 2, 'Table'[Value] *13/1555, rowcount = 3, 'Table'[Value] *17/1555, rowcount = 4, 'Table'[Value] *40/1555, rowcount = 5, 'Table'[Value] *75/1555, 0)

3.Here's my final result, which I hope meets your requirements.

Please find the attached pbix relevant to the case.

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: power bi DAX formula to calculate percentage from a column with multiple values

Khushboo9966 — Thu, 20 Jun 2024 17:21:39 GMT

Anonymous Hi, Your solution works when the 'rowcount' is <10. I have 'rowcount' values >10 in all cases and in this case the formula returns zero.

The solution I have adopted is I created two seperated calculated colums named '_Count by Analyzed By by Value' and '%'. The result when i graph it out but the issue for me now duplicates rows as below:

So when put them in a matrix form, I have select avg where I want the sum which is (64%). see below ex:

Re: power bi DAX formula to calculate percentage from a column with multiple values

Anonymous — Mon, 24 Jun 2024 06:46:39 GMT

Hi,Khushboo9966

Regarding the issue you raised, my solution is as follows:

1.Create calculated table references:

new = VAR _summarize = SUMMARIZE ( 'Table', [Analyzed By], "Value", " Total", "%", SUM ( 'Table'[%] ) ) RETURN UNION ('Table', _summarize )

2.Here's my final result, which I hope meets your requirements.

3.Here is the relevant documentation:

How to put the row and column subtotals in front o... - Microsoft Fabric Community

Please find the attached pbix relevant to the case.

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.