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

[Khushboo9966]

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

[Anonymous · Accepted solution]

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.

[Khushboo9966]

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,

 0)

[Anonymous · Accepted solution]

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.

[Khushboo9966]

@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:

[Anonymous · Accepted solution]

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.