Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Weightage based on unique value

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:

PortfolioCustomer IDNominal amountWeightage
PFID551600,0000.705882
PFID602200,0000.117647
PFID603100,0000.058824
PFID554100,0000.117647
PFID605500,0000.294118
PFID556150,0000.176471
PFID607300,0000.176471
PFID568300,0000.75
PFID659250,0000.263158
PFID561050,0000.125
PFID5711200,0000.285714
PFID6012100,0000.058824
PFID561350,0000.125
PFID5714200,0000.285714
PFID6515700,0000.736842
PFID5716300,0000.428571
PFID6017500,0000.294118



  • Jihwan_Kim's avatar
    Jihwan_Kim
    1 year ago

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

4 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    1 year ago

    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] )
    )
  • Kedar_Pande's avatar
    Kedar_Pande
    Super User
    1 year ago

    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

  • v-aatheeque's avatar
    v-aatheeque
    Community Support
    1 year ago

    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.

  • Poojara_D12's avatar
    Poojara_D12
    Super User
    1 year ago

    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