cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
GilesWalker Established Member
Established Member

Help with sum of distinct count filter

Hi everyone,

 

I am new to using DAX and was hoping I could get help with this issue.

 

I have created a column which combines a text field (Wagon name) with a number field (wagon number). I then use this to create a DISTINCTCOUNT for a given time period.

 

In another column I have used the formula Wagons total:= if(Wagon name="IB",3,2). Each wagon can only hold 2 items except the IB wagons which can hold 3.

 

What I am trying to do is sum Wagons Total based off of what the DISTINCTCOUNT picks up.

 

I have tried a bunch of different formula but to no avail. Hope you can help.

 

Thanks,

 

Giles

1 ACCEPTED SOLUTION

Accepted Solutions
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.
20 REPLIES 20
Super User
Super User

Re: Help with sum of distinct count filter

Have you been out here:

http://www.daxpatterns.com/patterns/

 

 

Down near the bottom are a couple patterns that may help you.


I have book! Learn Power BI from Packt


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

Proud to be a Datanaut!

Super User
Super User

Re: Help with sum of distinct count filter

In thinking through this, is there any reason why you couldn't just use a matrix visualization based on your unique Wagon field and filter it by time period?

 

Can you provide some sample data along with what your expected output is for that sample data?


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

My apologies for not replying sooner. I will check out this page and see what I can find.

GilesWalker Established Member
Established Member

Re: Help with sum of distinct count filter

I have had a look at the link you supplied and tried a few of the formula in there but still no answer.

 

I can show an example of the data, however I am unsure of how to do that in here. I have included a picture with some of the information. 

 

The count of wagon and number shows 27 - this is the correct number of unique wagons.

 

The TEU-FEU containers shows 55 - This is how many slots were actually filled out of the 27 wagons.

 

However total wagon capacity shows 84 - it should show 56. The reason it is showing 84 is it is not filtering down off of the unique wagon.

 

The table shows the non summarized total wagon capacity. All i am trying to do is sum these numbers, the sum of these numbers is 56.Wagons.JPG

ALeef Member
Member

Re: Help with sum of distinct count filter

How about trying something like:

 

Total Wagon Capacity (Measure) =  SUMX( CALCULATETABLE('wagontable'[capacity], FILTER(DISTINCT('wagontable'[wagonname]), FILTER(TimeCriteria = whatever you need)))

 

If I'm understanding your data structure/layout - that will look at your wagontable, filter out a list of distinct wagon names, filter that by your time criteria, and then sum up the results of the capacity column, once it has the filtered list.

 

 

Rémi Member
Member

Re: Help with sum of distinct count filter

Hi,

 

Can you try something like that :

 

Real total = SUMX(SUMMARIZE('Wagons table',[Wagon and number],"Total per wagon",[Total wagon capacity]),[Total per wagon])

 

So you evaluate your [Total wagon capacity] for each row and you sum the result, it should work.

scottsen Senior Member
Senior Member

Re: Help with sum of distinct count filter

Distinct Total := SUMX(VALUES(MyTable[Unique]), MIN(MyTable[Wagons Total]))

 

From what I gather... you have duplicate MyTable[Unique]  (Unique is WagonName + WagonNumber) ?   the VALUES() function will return a table of just the distinct values.  For each distinct/unique value, you want to grab the Wagon Total, but since there are multple rows with the same unique, you need to use an aggregate (I chose MIN).  From what I understand, it shouldn't matter which aggregate, as I assume they all share the same WagonsTotal.

 

If my assumptions are wrong, we will have to re-assess Smiley Happy

 

GilesWalker Established Member
Established Member

Re: Help with sum of distinct count filter

Thank you eveyone for your help but I still cant get it to work (probably operator error).

 

Here is the data it is looking at. In Power Bi i have created columns S and T. If you total column T the answer is 84 (this is what I get everytime). If you manualy calculate the the unique wagons the answer is 56.

 

I hope this data now helps.

 

IdOrder DatePackContainerWagonNumberWagon and NuTotal wagon capacity
299515709-Oct-15C20BSHB0826921IB145IB-1453
299501809-Oct-15C20ETNU1200136IB358IB-3583
299501909-Oct-15C40GLDU9946962IB358IB-3583
299515809-Oct-15C20TSHB9680IB145IB-1453
299516009-Oct-15C20BSHB0824424TQAY14TQAY-142
299516309-Oct-15C20BSHB0842033TQAY110TQAY-1102
299516409-Oct-15C20BSHB0844720TQAY110TQAY-1102
299522109-Oct-15C20BSTB0834871TQAY85TQAY-852
299521709-Oct-15C20CMAU1173638TQAY117TQAY-1172
299506609-Oct-15C40CMAU4648932TQAY08TQAY-82
299506509-Oct-15C40CMAU4672353TQAY23TQAY-232
299502009-Oct-15C40CMAU4925641TQAY22TQAY-222
299502609-Oct-15C40CMAU5236945TQAY115TQAY-1152
299522809-Oct-15C20DFSU2855550TQAY109TQAY-1092
299502409-Oct-15C40ECMU9933220TQAY72TQAY-722
299501509-Oct-15TRNETNU1200007TQAY88TQAY-882
299502509-Oct-15C40GESU5019003TQAY41TQAY-412
299502309-Oct-15C40MAGU5735219TQAY61TQAY-612
299502209-Oct-15C40SEGU4106290TQAY62TQAY-622
299506109-Oct-15C40SEGU4757774TQAY75TQAY-752
299502109-Oct-15C40TCNU4588222TQAY21TQAY-212
299522409-Oct-15C20TEMU5871303TQAY117TQAY-1172
299506309-Oct-15C40TGHU8766469TQAY78TQAY-782
299505709-Oct-15C40TGHU9488301TQAY116TQAY-1162
299522209-Oct-15C20TRTU0821773TQAY108TQAY-1082
299516109-Oct-15C20TRTU0823117TQAY39TQAY-392
299521909-Oct-15C20TSHB0822110TQAY84TQAY-842
299515909-Oct-15C20TSHB1044717TQAY14TQAY-142
299501609-Oct-15C20TSHB9265TQAY88TQAY-882
299516209-Oct-15C20TSHB9449TQAY39TQAY-392
299522609-Oct-15C20TSHB9499TQAY109TQAY-1092
299521809-Oct-15C20TSHB9565TQAY84TQAY-842
299522009-Oct-15C20TSHB9622TQAY85TQAY-852
299522309-Oct-15C20TSHB9648TQAY108TQAY-1082
299522509-Oct-15C20CMAU2168240TQMF03TQMF-32
299506409-Oct-15C40DFSU4131980TQMF11TQMF-112
299501709-Oct-15C40TEMU7345463TQMF02TQMF-22
299522709-Oct-15C20TRTU0822635TQMF03TQMF-32
299506709-Oct-15C20TSHB9285TQMF10TQMF-102
299506809-Oct-15C20TSHB9605TQMF10TQMF-102
GilesWalker Established Member
Established Member

Re: Help with sum of distinct count filter

just realised that you cant see column S and T. The columns are the Wagon and Nu and Total wagon capacity

Helpful resources

Announcements
Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 299 members 2,876 guests
Please welcome our newest community members: