Skip to main content
cancel
Showing results for 
Search instead 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

Reply
GilesWalker
Skilled Sharer
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
leonardmurphy
Skilled Sharer
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.

View solution in original post

20 REPLIES 20
Parihar1980
Helper II
Helper II

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

 

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

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.

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.

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.

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 🙂

 

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

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

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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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

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.

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

 

 

Greg_Deckler
Super User
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?


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

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

Have you been out here:

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

 

 

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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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

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

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.

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.

 

 

Helpful resources

Announcements
Europe Fabric Conference

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

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