cancel
Showing results for
Did you mean:
Frequent Visitor

## Percentage Calculation

Hi Power BI Community User,

I need your input while calculating the percentage column which is unique requirement.

The first two rows in the below screen shot is to be divided by 'value' of 'attribute' = abc and the last three rows need to be divided by value' of 'attribute' = def.

Any leads to this solution would be highly appreciated.

Some more input: I have tried the following, but was stuck further.

Perc =
VAR CurrSel = CALCULATE(SUM(FCT_TBL[VALUE]))
VAR Total = CALCULATE(SUM(FCT_TBL[VALUE]),ALL(FCT_TBL),[ATTRIB] = 'abc')
RETURN
DIVIDE (CurrSel, Total)

Best regards,

Manish Kumar

8 REPLIES 8
Frequent Visitor

Thank you everyone.

Community Support

Hi @manish155 ,

Best Regards,

Liu Yang

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

Community Support

Hi  @manish155 ,

Here are the steps you can follow：

1. Increase the index through power query

2. Create calculcated column.

``````pc value =
IF('Table'[Index]<=2,
DIVIDE('Table'[value],CALCULATE(SUM('Table'[value]),FILTER('Table','Table'[attrib]="abc"))),
DIVIDE('Table'[value],CALCULATE(SUM('Table'[value]),FILTER('Table','Table'[attrib]="def"))))``````

3. Result.

Best Regards,

Liu Yang

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

Solution Specialist

Hey @manish155 ,
You can create a flag that identifies the abc or def attribute which will be used to identify the denominator and then create a calculate column as shown below

`PCT Calculation = var abc=CALCULATE(SUM('Table'[value]),'Table'[attrib]="abc",ALL('Table'))var def=CALCULATE(SUM('Table'[value]),'Table'[attrib]="def",ALL('Table'))var calc=IF('Table'[ABC]=1,DIVIDE('Table'[value],abc,BLANK()),DIVIDE('Table'[value],def))return calc`

Please accept this as solution if this helps !! Appreciate a Kudos 😀

Frequent Visitor

Hi @vivran22 ,

Yes there are additional row. However, there are only attributes (abc & def) as denominator. The rows with attrib in (xyz,abc) should be divided by abc .All other rows are to be divided by def

Regards,
Manish
Super User I

You may try this as calculated column:

``````Percent =
VAR _ABC = LOOKUPVALUE('Table'[Value],'Table'[attrib] , "abc")
VAR _DEF = LOOKUPVALUE('Table'[Value],'Table'[attrib] , "def")
VAR _CurrentAT = 'Table'[attrib]
VAR _Divide = DIVIDE('Table'[Value],IF(_CurrentAT in  {"abc","xyz"},_ABC,_DEF))
RETURN
_Divide``````

Cheers!
Vivek

If it helps, please mark it as a solution. Kudos would be a cherry on the top 🙂
If it doesn't, then please share a sample data along with the expected results (preferably an excel file and not an image)

Blog: vivran.in/my-blog

Frequent Visitor

Hi Vivek,

When I am replicating the same in my issue, the fourth line gives me the following error 'Cannot find the name '[attrib]'

BR,

Manish Kumar

Super User I

Hey @manish155 ,

Are there only two attributes as denominator (abc & def)? Also, I am assuming there must be additional number of rows in the table with more attributes. How will you identify the denominator for each row?

Cheers!
Vivek

Blog: vivran.in/my-blog

Announcements