cancel
Showing results for
Did you mean:
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
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

## 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

Proud to be a Datanaut!

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

Proud to be a Datanaut!

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.

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.

Member

## Re: Help with sum of distinct count filter

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.

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.

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

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.

 Id Order Date Pack Container Wagon Number Wagon and Nu Total wagon capacity 2995157 09-Oct-15 C20 BSHB0826921 IB 145 IB-145 3 2995018 09-Oct-15 C20 ETNU1200136 IB 358 IB-358 3 2995019 09-Oct-15 C40 GLDU9946962 IB 358 IB-358 3 2995158 09-Oct-15 C20 TSHB9680 IB 145 IB-145 3 2995160 09-Oct-15 C20 BSHB0824424 TQAY 14 TQAY-14 2 2995163 09-Oct-15 C20 BSHB0842033 TQAY 110 TQAY-110 2 2995164 09-Oct-15 C20 BSHB0844720 TQAY 110 TQAY-110 2 2995221 09-Oct-15 C20 BSTB0834871 TQAY 85 TQAY-85 2 2995217 09-Oct-15 C20 CMAU1173638 TQAY 117 TQAY-117 2 2995066 09-Oct-15 C40 CMAU4648932 TQAY 08 TQAY-8 2 2995065 09-Oct-15 C40 CMAU4672353 TQAY 23 TQAY-23 2 2995020 09-Oct-15 C40 CMAU4925641 TQAY 22 TQAY-22 2 2995026 09-Oct-15 C40 CMAU5236945 TQAY 115 TQAY-115 2 2995228 09-Oct-15 C20 DFSU2855550 TQAY 109 TQAY-109 2 2995024 09-Oct-15 C40 ECMU9933220 TQAY 72 TQAY-72 2 2995015 09-Oct-15 TRN ETNU1200007 TQAY 88 TQAY-88 2 2995025 09-Oct-15 C40 GESU5019003 TQAY 41 TQAY-41 2 2995023 09-Oct-15 C40 MAGU5735219 TQAY 61 TQAY-61 2 2995022 09-Oct-15 C40 SEGU4106290 TQAY 62 TQAY-62 2 2995061 09-Oct-15 C40 SEGU4757774 TQAY 75 TQAY-75 2 2995021 09-Oct-15 C40 TCNU4588222 TQAY 21 TQAY-21 2 2995224 09-Oct-15 C20 TEMU5871303 TQAY 117 TQAY-117 2 2995063 09-Oct-15 C40 TGHU8766469 TQAY 78 TQAY-78 2 2995057 09-Oct-15 C40 TGHU9488301 TQAY 116 TQAY-116 2 2995222 09-Oct-15 C20 TRTU0821773 TQAY 108 TQAY-108 2 2995161 09-Oct-15 C20 TRTU0823117 TQAY 39 TQAY-39 2 2995219 09-Oct-15 C20 TSHB0822110 TQAY 84 TQAY-84 2 2995159 09-Oct-15 C20 TSHB1044717 TQAY 14 TQAY-14 2 2995016 09-Oct-15 C20 TSHB9265 TQAY 88 TQAY-88 2 2995162 09-Oct-15 C20 TSHB9449 TQAY 39 TQAY-39 2 2995226 09-Oct-15 C20 TSHB9499 TQAY 109 TQAY-109 2 2995218 09-Oct-15 C20 TSHB9565 TQAY 84 TQAY-84 2 2995220 09-Oct-15 C20 TSHB9622 TQAY 85 TQAY-85 2 2995223 09-Oct-15 C20 TSHB9648 TQAY 108 TQAY-108 2 2995225 09-Oct-15 C20 CMAU2168240 TQMF 03 TQMF-3 2 2995064 09-Oct-15 C40 DFSU4131980 TQMF 11 TQMF-11 2 2995017 09-Oct-15 C40 TEMU7345463 TQMF 02 TQMF-2 2 2995227 09-Oct-15 C20 TRTU0822635 TQMF 03 TQMF-3 2 2995067 09-Oct-15 C20 TSHB9285 TQMF 10 TQMF-10 2 2995068 09-Oct-15 C20 TSHB9605 TQMF 10 TQMF-10 2
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

Announcements

#### Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

#### Community News & Announcements

Get your latest community news and announcements.

#### 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.

#### Win Power BI Swag with Community Kudopalooza!

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

Top Kudoed Authors
Users Online
Currently online: 136 members 1,604 guests
Recent signins: