Counting the number of reference occurences in a list depending a quantity

[GuillaumeL06]

Hello everyone,

I have a column of article references called « Article » and a quantity (column called « Qty ») which counts for each article reference how many article I need. The column « site » refers to the location where the article has to be delivered.

My request is :

I would like to have a column (called "total QTY") with a DAX expression which counts the occurrence number of article depending its quantity. It will be the total quantity for each article reference. For example, for the article «1 », I would like to have a total quantity equals to 7, for article « 2 », I would like to have a total qty equals to 1 etc.

Article                  Site                       QTY                      Total QTY

1                           SQY                      5                           7

1                           SAESB                  2                           7

2                           CTC                      1                           1

3                           SQY                      1                           8

3                           SAESB                  5                           8

3                           SAESA                  2                           8

8                           SQY                      1                           2

8                           SAESA                  1                           2

Thanks for your answers

[ntaylo06]

This probably isn't best practice and probably adds redundant process time, but I have solved similar issues by:

1. Creating a new query based off of the same data source,

2. Removing all columns except what you want to count

3. Adding a calculated column that counts total

4. Creating a relationship with the full query and adding the calculated column to your visual

It makes the column slicerproof and able to be displayed right next to the "sliced" data.

[parry2k · Accepted solution]

@GuillaumeL06 try this measure

Total Qty = CALCULATE( SUM(Table[Qty] ), ALLEXCEPT( Table, Table[Article]))