cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Super User
Super User

Re: Help with sum of distinct count filter

scottsen's solution worked for me using your data:

 

Measure 5 = SUMX(DISTINCT(Sheet1[Wagon and Nu]), MIN([Total wagon capacity]))

 

Got 54

 

Imported your data from an Excel spreadsheet into table "Sheet1".


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

GilesWalker Established Member
Established Member

Re: Help with sum of distinct count filter

smoupre,

 

Thanks for checking this however when using the MIN function the answer doesnt count the IB wagons correctly. The number I am looking for is 56.

 

There are 2 IB wagons = 6 spaces

There are 21 TQAY wagons = 42 spaces

There are 4 TQMF wagons = 8 spaces

 

Total - 56 spaces.

 

If you change the function from MIN to MAX you get an answer of 81.

konstantinos Senior Member
Senior Member

Re: Help with sum of distinct count filter

Extension to @scottsen....I didn't had to much time but can you try as giving the correct result..

 

Capacity :=
SUMX (
    VALUES ( Table1[Wagon and Nu] );
    DIVIDE (
        CALCULATE ( SUM ( Table1[Total wagon capacity] ) );
        CALCULATE ( COUNTROWS ( Table1 ) )
    )

 

Konstantinos Ioannou
ALeef Member
Member

Re: Help with sum of distinct count filter

After looking at the data, this is working for me.  I changed SUMX to SUM and moved the calculate outside.  Hopefully it works for you.

 

Total Wagon Capacity (Measure) =  CALCULATE( SUM('wagontable'[Total Wagon Capacity]), FILTER('wagontable', DISTINCT('wagontable'[Wagon and Nu])))

 

 

leonardmurphy Established Member
Established Member

Re: Help with sum of distinct count filter

Given the table of data (which is helpful), Rémi's answer is close to what I would suggest.

 

SUMX(SUMMARIZE(TableName, [Wagon and Nu], [Total wagon capacity]), [Total wagon capacity])

 

*changing TableName to the actual name of your table.

 

The SUMMARIZE creates a filtered table with just the DISTINCT values of [Wagon and Nu] and [Wagon Capacity]. In other words, it gets rid of the duplicate rows (based on the 2 columns listed), then sums the capacity of the remaining unique rows.

 

In other words:

 

IB-145 3

IB-358 3

IB-145 3

IB-358 3

 

is turned into simply

 

IB-145 3

IB-358 3

 

And the 2nd column is then summed (which is this small example becomes 6).

 

If you have any choice over how the data is stored though, I'd recommend splitting your data into two actual tables. In one table, have the ID, [Order Date], Pack, Container, [Wagon & Nu] only. In another table have [Wagon & Nu], Wagon, Number and Capacity split out. In this 2nd table (the lookup table), you only need one row per [Wagon & Nu]. Join the two tables on the key field [Wagon & Nu] and in this situation, a simple SUM on capacity would work.

---
In Wisconsin? Join the Microsoft BI Professionals - Wisconsin group.
ALeef Member
Member

Re: Help with sum of distinct count filter

Because he isn't having to sum iteratively, (ie. a quantity in a wagon * a price, and then sum each of those) - sumx isn't necessary - could use plain sum.

 

What he needs is the sum of a single column table of the wagon capacities, that has been filtered to only have distinct values for the wagon-type/number combination.  

 

I haven't worked much with summarize, but it looks like it should do the trick based on your explanation.

leonardmurphy Established Member
Established Member

Re: Help with sum of distinct count filter

SUMX is required to specify the table using SUMMARIZE. I couldn't get plain SUM to work with SUMMARIZE.

 

Performance wise, the SUMX is iterating over the rows of the summarized table, not the entire table. (Still not ideal though)

 

I'm certainly not saying that mine is the only (or even the best) solution though. Several of the solutions above (especially yours, ALeef, with CALCULATE and FILTER) seemed like they should work, even though I struggled. I figured if I struggled, the OP might too, so I added my 2 cents.

 

And honestly, reorganizing the data so there's a [wagon & num] lookup table would be the best solution. Lookup tables will pay dividends over & over again as the data model gets more complex.

---
In Wisconsin? Join the Microsoft BI Professionals - Wisconsin group.
ALeef Member
Member

Re: Help with sum of distinct count filter

Totally agree.  Getting the data model lookup table would be a much cleaner way of doing it in the long run.  

 

Like I said, haven't played with Summarize much.  Everybody's learning, hopefully he finds a solution that works for him.

GilesWalker Established Member
Established Member

Re: Help with sum of distinct count filter

Thank you everybody for your help with this issues, it is now working.

Parihar1980 Regular Visitor
Regular Visitor

Re: Help with sum of distinct count filter

Please help me to solve this

 

Select Count(distinct) , Col From Table

Group by Col 

 

 

i am not getting result with below expression 

 

 

=SUMMARIZE(Table , Table[Col] ,

Countrows(DISTINCT(table[Col])))

 

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 258 members 2,942 guests
Please welcome our newest community members: