Forum Discussion

murali5431's avatar
murali5431
Helper III
5 years ago
Solved

Reprt builder expression for calculation based on distinct values in a column

Hi,   I have a power BI report builder which has duplicated rows (column A) as below. I need to calculate sum of column B based on unique values in column A. Please let me know the report builder e...
expressions
Report Builders
  • Anonymous's avatar
    Anonymous
    5 years ago

    murali5431 
    A simple measure should do:

    Measure = SUMX(SUMMARIZE('Table',[Column A],'Table'[Column B]),[Column B])
     
    And if there are always 2 repeated values, you can also use:
    Measure = SUMX(DISTINCT('Table'[Column B]),[Column B])

     


    Paul Zheng _ Community Support Team
    If this post helps, please Accept it as the solution to help the other members find it more quickly.
HashamNiaz's avatar
HashamNiaz
Solution Sage
5 years ago

Hi !

Does the values in ColumnB will always be exactly same for repeted ColumnA members. How you want the calculation to work if you have have PartA with 2 distinct values in ColumnB, 150 & 120. What you want to do in this scenrio.

 

Regards,

  • murali5431's avatar
    murali5431
    Helper III
    5 years ago

    HashamNiaz 

    Values in ColumnB will always be exactly same for repeted ColumnA members, so we can ignore the scenario of columnB having different values for a value in column A. 

    Thanks for checking!

     

    Regards,

    Muralidhar

    • HashamNiaz's avatar
      HashamNiaz
      Solution Sage
      5 years ago

      Hi !

      I would solve this like below;

       

      1) Create a new table using DAX;

      Go to Modelling Tab -> New Table, provide following DAX

      NewTable = SUMMARIZE(Table, Table[ColumnA], Table[ColumnB])


      Table is the name of your table, you can replace it with your table name in the formula.

       

      Now create a measure to sum column B, use following DAX

      NewSum = SUM(NewTable[ColumnB])

       

      Hope this will solve your requirement.

       

      Regards,

      Hasham