Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Thank you everyone.
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.
Hi @Anonymous ,
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.
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.
Hey @Anonymous ,
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 😀
Hi @vivran22 ,
@Anonymous
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
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
Hey @Anonymous ,
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |