Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Oceans88
Helper III
Helper III

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. 

 

Capture4.PNG

1 ACCEPTED SOLUTION

@Oceans88  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?

jdbuchanan71_0-1618692718521.png

jdbuchanan71_1-1618692761967.png

 

View solution in original post

13 REPLIES 13
LeapSheep
Regular Visitor

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?

Not Sum Data.PNG

Oceans88
Helper III
Helper III

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"
jdbuchanan71
Super User
Super User

@Oceans88 

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

jdbuchanan71_0-1618607294369.png

 

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

@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

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

2021-04-17_0-53-50.png

@jdbuchanan71 

 

Hi thanks again for looking into it. Im more looking for the first example you provided at the begining but would need it in the form of a Calculated table i dont need the Sum of all the numbers like your last example. 

@jdbuchanan71  I am sorry i guess i was missing the Calculated table portion adn did not explain correctly. 

Oh, for that you can just do 

New Table > 

New Table = SUMMARIZE ( YourTable, YourTable[Cycle.Name], YourTable[Cycle.Breakup] )

@jdbuchanan71 

 

Source Data

Oceans88_1-1618690374633.png

 

Calculated table that is needed

Oceans88_2-1618690499567.png

 

 

 

@Oceans88  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?

jdbuchanan71_0-1618692718521.png

jdbuchanan71_1-1618692761967.png

 

@jdbuchanan71 

 

You are 100% correct. Its my mistake. I just checked the table and it works perfectly. I overlooked that since the numbers on breakup are numerical values they get sumed up automaticaly when creating a table on a page.

 

Many thanks for your help. Greatly apreciated. 

 

Kind regards.

Hi , thank you for the dax, but its summing the breakup still, we need it to be not summing the breakup. Do you know how i could acheive that?

 

 

Oceans88_0-1618689819435.png

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.