cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
manish155
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.

 

manish155_0-1605626905646.jpeg

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
manish155
Frequent Visitor

Thank you everyone.

v-yangliu-msft
Community Support
Community Support

Hi @manish155 ,

Please change the statement marked with red to the actual field name in your pbix file. If you still have problems, please upload your pbix file

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.

v-yangliu-msft
Community Support
Community Support

Hi  @manish155 ,

Here are the steps you can follow:

1. Increase the index through power query

v-yangliu-msft_0-1605747486285.jpeg

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.

v-yangliu-msft_1-1605747486288.jpeg

You can downloaded PBIX file from here.

 

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.

jaideepnema
Solution Specialist
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

jaideepnema_0-1605638442070.png

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 😀

manish155
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

@manish155 

 

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

 

image.png

 

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
Connect on LinkedIn
Follow on Twitter

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

vivran22
Super User I
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
Connect on LinkedIn
Follow on Twitter

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.