Help with sum of distinct count filter

[GilesWalker]

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

[Parihar1980]

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 · Accepted solution]

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.

[Anonymous]

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

[ALeef]

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]

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.

[ALeef]

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]

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

[Anonymous]

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 🙂

[GilesWalker]

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

[GilesWalker]

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

[Greg_Deckler]

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

[konstantinos]

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

[GilesWalker]

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.

[ALeef]

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]

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?

[Greg_Deckler]

Have you been out here:

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

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

[GilesWalker]

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

[GilesWalker]

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.

[Rémi]

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.

[ALeef]

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.