Weightage based on unique value

[keith515]

Hi all, any advice in relation to the following problem would be greatly appreciated.

Background to problem:

I have a data table with portfolio ID column which there will be several customers under same portfolio ID.  In my previous excel working I am using the nominal amount from each customer ID divided by the sum up the total nominal of each PFID for the weightage. How do I build a formula column in Power BI table that return the weightage amount based on each unique customer ID? 

Example as below with the calculation I did in Excel:

Portfolio	Customer ID	Nominal amount	Weightage
PFID55	1	600,000	0.705882
PFID60	2	200,000	0.117647
PFID60	3	100,000	0.058824
PFID55	4	100,000	0.117647
PFID60	5	500,000	0.294118
PFID55	6	150,000	0.176471
PFID60	7	300,000	0.176471
PFID56	8	300,000	0.75
PFID65	9	250,000	0.263158
PFID56	10	50,000	0.125
PFID57	11	200,000	0.285714
PFID60	12	100,000	0.058824
PFID56	13	50,000	0.125
PFID57	14	200,000	0.285714
PFID65	15	700,000	0.736842
PFID57	16	300,000	0.428571
PFID60	17	500,000	0.294118

[Poojara_D12]

Hi @keith515 

• Go to the table in Power BI where your data resides.
• Click on the Modeling tab and select New Column.
• Use the following DAX formula:

Weightage = 
VAR PortfolioID = [Portfolio] 
VAR TotalNominalForPortfolio = 
    CALCULATE(
        SUM('YourTable'[Nominal amount]),
        ALLEXCEPT('YourTable', 'YourTable'[Portfolio])
    )
RETURN 
    DIVIDE([Nominal amount], TotalNominalForPortfolio, 0)
​

• Example Output:
  
• Ensure the "Portfolio" and "Nominal amount" columns are correctly formatted and named.

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Poojara
Data Analyst | MSBI Developer | Power BI Consultant
Consider Subscribing my YouTube for Beginners/Advance Concepts: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS 

[v-aatheeque]

Hi @keith515 

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet.
In case if you have any resolution please do share that same with the community as it can be helpful to others.
Otherwise, will respond back with the more details and we will try to help.

[Kedar_Pande]

@keith515 

Calculated column:

Weightage = 
'Table'[Nominal amount] / 
CALCULATE(
SUM('Table'[Nominal amount]),
ALLEXCEPT('Table', 'Table'[Portfolio])
)

💌 If this helped, a Kudos 👍 or Solution mark ✅ would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

It is for creating a calculated column.

Weightage CC = 
VAR _portfolio = Data[Portfolio]
RETURN
    DIVIDE (
        Data[Nominal amount],
        SUMX ( FILTER ( Data, Data[Portfolio] = _portfolio ), Data[Nominal amount] )
    )