Measurement in the Table

[legato]

Goal:
Have a measurement for each PK_test row based on sum of all its quantity from productNumber.


PK_test qty productNumber
-----------------------------------------------
1 5 asdf
2 4 asdf
3 9 sdfg
4 1 sdfg
5 1 lkj
6 6 lkj
7 3 lkj
8 3 mnb



To make it more easier to understand the result will be of the mesure "m"

PK_test qty productNumber m
-----------------------------------------------
1 5 asdf 9
2 4 asdf 9
3 9 sdfg 10
4 1 sdfg 10
5 1 lkj 10
6 6 lkj 10
7 3 lkj 10
8 3 mnb 3


Problem:
How should I create a calculated measure "m"?


Info:
- No SQl code
- I'm using SSAS tabular
- The value shall not be changed when you select one or many column in dimension table(s). In order word, the value will not be altered based on selection.

 

[Oeldere]

c1=SUMIF($B$1:$B$8,$B1,$A$1:$A$8) and drag down.If you want only the values per criteria, you can also use a pivot table

 

[legato]

I don't really understand the code and its context. Im using SSAS tabular in SQL server 2012.

 

[Laurent C]

The above formula is an Excel formula.

Regarding your query, it seems you want to get the sum of qty for the currently selected product, whatever other criteria are. Right?

 

[legato]

You can say that total quantity for each productNumber for instance there are two row of productNumber "asdf" and the total value is 9 that should be applied for these two rows.
It should be a caculcated measure. The goal is to have this ability as a calculated measure, not calculated column.

The reason why I have the table with its value is because to faciliate the communication.

The above formula is an Excel formula.

Regarding your query, it seems you want to get the sum of qty for the currently selected product, whatever other criteria are. Right?

 

[legato]

I also believe that you need to use summerize but I don't how to apply it in reality because I got lots of bug error.

You can say that total quantity for each productNumber for instance there are two row of productNumber "asdf" and the total value is 9 that should be applied for these two rows.
It should be a caculcated measure. The goal is to have this ability as a calculated measure, not calculated column.

The reason why I have the table with its value is because to faciliate the communication.

 

[Laurent C]

An expression along this line, should do the trick:

CALCULATE(
SUM( TheTable[qty] )
, ALL( TheTable )
, VALUES( TheTable[productNumber] )
)

With a table called Facts and a dimension table called Product, this could look like this:

CALCULATE(
SUM( Facts[qty] )
, ALL( Facts )
, VALUES( Product )
)