cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Skilled Sharer

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

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 Madison Power BI User Group.
20 REPLIES 20
Helper II

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

Skilled Sharer

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 Madison Power BI User Group.
Anonymous
Not applicable

this is what i have done in the past

CALCULATE (
SUM ( Methods[F_Not_Covered_Lines] ),
DISTINCT (
SUMMARIZE (
Methods,
Methods[ModuleName],
Methods[NamespaceName],
Methods[ClassName]
)
)

So use summarize to summarize the data to the level you want above the grain-> wrap it in a distinct -> pass it as a filter to a calculate function and do your aggregation

Resolver II

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.

Skilled Sharer

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 Madison Power BI User Group.
Resolver II

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.

Skilled Sharer

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

Anonymous
Not applicable

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 🙂

Skilled Sharer

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
Skilled Sharer

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

Super User

scottsen's solution worked for me using your data&colon;

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

Got 54

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Memorable Member

Extension to @Anonymous....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
Skilled Sharer

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.

Resolver II

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

Super User

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?

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Super User

Have you been out here:

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

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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Skilled Sharer

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

Skilled Sharer

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.

Resolver III

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.

Resolver II

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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors