How to filter a measure by all values

[mumair]

So currently I have DAX code as follows:

Measure = sumx(FILTER(Qty, Qty[ID #] = "53101"), [Sales])

This correctly sums up all the sales for a given ID. However, I want to do this for all sales across all IDs. How do I do this? Also, sales is a measure and not a column if that makes a difference.

[ibarrau]

Hi there. I'm sorry, but you should tell us more details about tables and the measure sales in order to help. If you just need sum a column of a table with and axis of another table, just relate them in Relationships View. Then you can add the columns in a single visualization set the numeric one to sum and you are ready to go.

Regards,

[mumair]

Let's say my table looks like this

ID # | Sales Amount

01 | 2

01 | 3

01 | 3

01 | 5

01 | 1

01 | 3

02 | 5

02 | 6

02 | 3

03 | 3

03 | 1

I want ONE DAX function that will sum up the sales amount per ID number. This data is hypothetical. My real dataset is much longer and has more columns, but for the time being, I am only concerned about these two particular columns. The sales amount is measure generated from another table.

[v-shex-msft]

Hi @mumair,

You can use max function to get current row content, then use it as the varibale to filter all table.

Below formula will summary amounts with has the same id:

Total= SUMX(FILTER(ALL(Table),[ID]=MAX(Table[ID])),[Sales Amount])

Regards,

Xiaoxin Sheng

[ibarrau]

Ok, that is not the table or an example of it because the measure is not from the table. Tell us the two tables involved, how are they related. Because at now, there is no problem. You can add the id and the measure to any visualization and it will work. Axis = not sumarize id and Value = Sales. You can make table, bar chart, etc.

Regards,

[mumair]

Alright, I have a time table and a quantity table. They are related by shipment number. One table has the shipments' quantities, and the other has the shipments' important times such as load and unload. I am interested in seeing the pump rate of different products on different ships. I calculated the pump rate for each shipment by dividing the metric tonnage of each shipment by the total loading time for each shipment. However, this is not the value I want as some shipments are on the same voyage and are the same product; thery are just for different customers and therefore registered as different shipments. What I want is the pump rate of all one type of cargo on the same ship, even if they go to different customers. Does this clarify things?

[ibarrau]

Yes, very much, now I get you need this measure to solve the issue. You need a way to SUM the sales of a DISTINCT table of IDs. I think this can help.

If Sales is already a SUM and the tables are related, then yo can do something like this:

= CALCULATE([Sales], SUMMARIZE(Table, Table[shipmentID])

or

= SUMX(DISTINCT(Table[ShipmentId]),[Sales])

Regards,

P/D I have never used the second way. I know the functions and in theory, they should work if the dependencies and sum are correct.

[mumair]

Neither of these ways worked. They didn't change the original measure at all. I am a little confused at what you mean by "if Sales is already a SUM" though? Could it be a problem if it isn't?

[ibarrau]

Ok, let check this. What is sales? you mention it as a measure. Can you send the DAX formula of it? so we can check and construct the new measure with the columns involved in the calculation of sales.

Regards,

[mumair]

I actually made up the sales and ID table for an illustration. One of my other posts details what I actually wanted.

Here's the DAX code for the measure:

Pump Rate =
(DIVIDE(SUM('Qty'[Vessel Measurement (MT)]), SUM('Time Log'[LoadTime]))) * 60

The vessel measurement is the amount of material on the ship for every shipment. The load time is the amount of time it took for the shipment to be loaded onto the vessel. The 60 is just to convert from minutes to hours.

[Stachu]

just to fully understand the data structure:

there are 2 tables:

Time Log

shipment nr	LoadTime
1	52
2	20
3	52
4	56
5	12

and Qty

shipment nr	Vessel Measurement (MT)
1	10
2	20
3	30
4	40
5	50

which have join on 'shipment nr' - is it 1:1 or 1:many relationship?
is there a separate table with the unique 'shipment nr' that joins the two?

[v-shex-msft]

Hi @Stachu,

Yes, if both two table contains the duplicate records, you should create a unique table to link them.(unique table one to many other tables)

Unique table sample:

Unique Table= DISTINCT(UNION(VALUES('Time Log'[shipment nr]),VALUES(Qty[shipment nr])))

>>which have join on 'shipment nr' - is it 1:1 or 1:many relationship?

Based on your sample data, I think it should be the one to one relationship on shipment nr column.

Regards,

Xiaoxin Sheng

[ibarrau]

I'm not undestanding the idea of the measure or it's calculation. Do you know how acomplish the value manually or thinking of topic?

I can help you with functions, like saying you can sum all values ignoring filtering with all or allexcept function and distinct to separate the ids like the sales example. SUMX(ALL(DISTINCT([id example])), Qty[Vessel Measurement (MT)]) 

If you have the manual calculation, go step by step, first try to get the wish number of the first argument in the DIVIDE and then the second, once having both, create the final result.

regards,