Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Aggregation without Sum

Hi All,

 

I was wondering if you can help my in this part. Im trying to aggregate some numbers without summing them. So for each cycle.Name i want to have each type of available numbers for it without summing them. Im sure im overlooking something simple here but not able to get it yet. 

 

Need Help
  • jdbuchanan71's avatar
    jdbuchanan71
    5 years ago

    Anonymous  the DAX I gave you should generate the new table you are looking for.  When you say "new table" you mean a new calucated table that is in your data model yes?

     

13 Replies

  • jdbuchanan71's avatar
    jdbuchanan71
    Super User
    5 years ago

    Anonymous 

    If you drag the field into the visual and set it to 'Don't Summarize' it will group them instead.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      jdbuchanan71 

       

      Thank you very much for the prompt reply. But i would need to have it made in dax as the numbers im looking for are basis used for another calculation behind the scenes. Visualy i dont need to display them anyware. Would you know how to produce same results by using dax? 

       

      THnak you & Kr

      • jdbuchanan71's avatar
        jdbuchanan71
        Super User
        5 years ago

        I think this will give you what you are looking for.

        Cycle Breakup Measure = 
SUMX (
    GROUPBY ( YourTable, YourTable[Cycle.Name], YourTable[Cycle.Breakup] ),
    YourTable[Cycle.Breakup]
)

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      This is exactly what i need but i need to create these setup in a calculated table. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Maybe somthing along these lines:

     

    55.Table.test =
    SELECTCOLUMNS('22_Price_Daily',"Name",'22_Price_Daily'[Cycle.Name],"Breakup",DISTINCT('22_Price_Daily'[Cycle.Breakup]))
     
     
    But its giving me error. " A table of multiple values was supplied where a single value was expected"
  • LeapSheep's avatar
    LeapSheep
    Regular Visitor
    5 years ago

    I am trying to create a dashboard that shows the value of the company by event attended but Power BI keeps summing the value by the number of both Invididual and Event. 

     

    See this example:

    I wanted to have a summary of the value we delivered to Company just by unique events they have attended and not adding up the value based on the number of attendees.

     

    For Company A, I wanted to see the total value we delivered adding from Founder Fundamentals Group Workshop (250), March 21 One on One (250), and April 21 One on One (250), total of 750. Power BI is adding up two entries from March 21 One on One so total becomes 1000. 

     

    Is there a way to de-duplicate this?