Measure that counts TOTAL of another measure in a table visualisation

[D3K]

Hey everyone! Please check the case below, can't find an answer for the task and need help

I have a visualisation, where a few columns from 2 different tables and a couple of measures.

This visualisation contains stores and products, it's sales and stocks results and the measure, which counts how many units of every product I need to order for any particular store.

So I need to write a measure that counts TOTAL SUM of order for every store.

As you can see below, there is for the Store1 we need to order 2 Products: SKU 1 for 48 Euro and SKU 2 for 24 Euro.  And how to write a measure, that will count SUM of that order - 72 Euro for Store 1?

Measure "Order, Money" counts as SUMX('Products List',[Order, quantity]*[Last price])

At the picture above I've placed a row, shows the type of data in visualisation

I've tried different combinations of ALL and ALLEXCEPT, but unsuccessful 🙂

Any help will be highly appreciated!

[amitchandak]

@D3K , Try a new column like

SUMX(filter('Products List',  [Store ID] = earlier([Store ID]) && [Address] = earlier([Address]) ,[Order, quantity]*[Last price])

[D3K]

I'll explain. This table visualisation is a dynamic: user can choose the number of days for for autoorder, percentage of increase/decrease for the order volume depended of different circumstances and so on.

That's why I need to make it definately as a measure, cause the simple table is static.

Thanks!

[v-shex-msft · Accepted solution]

Hi @D3K,

AFAIK, measure expression does not allow you direct use column.
You can use 'math' functions or selectedvalue to aggregate/extract the current value and use them as conditions for measure calculate:

Meaure =
VAR currAddress =
    SELECTEDVALUE ( Table[Address] )
VAR currID =
    MAX ( Table[Store ID] )
RETURN
    SUMX (
        FILTER ( ALLSELECTED ( Table ), [Store ID] = currID && [Address] = currAddress ),
        [Order, quantity] * [Last price]
    )

Using the SELECTEDVALUE function in DAX - SQLBI

Regards,

Xiaoxin Sheng

[D3K]

Is it possible to make like a measure, not like a new column? Without creating new table