topic Re: Weightage based on unique value in DAX Commands and Tips

Weightage based on unique value

Anonymous — Mon, 16 Dec 2024 03:49:01 GMT

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

Re: Weightage based on unique value

Jihwan_Kim — Mon, 16 Dec 2024 04:48:06 GMT

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] ) )

Re: Weightage based on unique value

Kedar_Pande — Mon, 16 Dec 2024 05:54:50 GMT

Anonymous

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

Re: Weightage based on unique value

v-aatheeque — Fri, 20 Dec 2024 10:00:50 GMT

Hi Anonymous

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.

Re: Weightage based on unique value

Poojara_D12 — Fri, 20 Dec 2024 10:41:39 GMT

Hi Anonymous

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