Sum with multiple filters

[Anonymous]

Hi, 

I would like to calculate sum of sales by period, channel and account. Which dax formula should i use ?

All these column are in the same table.

[v-yuta-msft]

@Anonymous ,

You may create a measure using DAX below:

Result =
CALCULATE (
    SUM ( Table[Sales] ),
    ALLEXCEPT ( Table, Table[Period], Table[Channel], Table[Account] )
)

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi , 

Attaching the powerbi for the data and pic for the desired result.

Result - As shown in the pic, I want a coloumn with sales by Account name and channel. With the suggested formula, I am uanble to create this column.Please click here for the .pbix

[Anonymous]

---

@v-yuta-msft wrote:

@Anonymous ,

You may create a measure using DAX below:

Result =
CALCULATE (
    SUM ( Table[Sales] ),
    ALLEXCEPT ( Table, Table[Period], Table[Channel], Table[Account] )
)

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

---

Hi ,

I am trying to get a column with a sum of lives by product by channel and account. 

Result should look like this -  Also attaching , the pbix for the data 

NewFactDrugCoverage_NI_DerivedFieldName

Pharmacy_Benefit_Janssen Account Name

Formulary  (2)_Coverage

NewFactDrugCoverage_NI_Channel

NewFactDrugCoverage_NI_DerivedFieldValue

NewFactDrugCoverage_NI_DrugName

NewFactDrugCoverage_NI_Period

NewFactDrugCoverage_NI_therapeuticclass

Pharm_Lives

Rank

Sum by account and channels

J&JImmuno_BioManagedStatusAKPharm

Aetna, Inc.

Covered Account

Managed Medicaid

Bio Managed 1

Remicade

201903

Immunology

50509

2

663485

J&JImmuno_BioManagedStatusAKPharm

Aetna, Inc.

Covered Account

Managed Medicaid

Bio Managed 2

Remicade

201903

Immunology

582953

1

663485

J&JImmuno_BioManagedStatusAKPharm

Aetna, Inc.

Covered Account

Managed Medicaid

Clinical Criteria Required

Remicade

201903

Immunology

30023

3

663485

J&JImmuno_BioManagedStatusAKPharm

Aetna, Inc.

Covered Account

Medicare

Clinical Criteria Required

Remicade

201903

Immunology

3506870

1

3512113

J&JImmuno_BioManagedStatusAKPharm

Aetna, Inc.

Covered Account

Medicare

PA or ST Details Unknown

Remicade

201903

Immunology

5243

2

NewFactDrugCoverage_NI_DerivedFieldName

Pharmacy_Benefit_Janssen Account Name

Formulary  (2)_Coverage

NewFactDrugCoverage_NI_Channel

NewFactDrugCoverage_NI_DerivedFieldValue

NewFactDrugCoverage_NI_DrugName

NewFactDrugCoverage_NI_Period

NewFactDrugCoverage_NI_therapeuticclass

Pharm_Lives

Rank

Sum by account and channels

J&JImmuno_BioManagedStatusAKPharm

Aetna, Inc.

Covered Account

Managed Medicaid

Bio Managed 1

Remicade

201903

Immunology

50509

2

663485

J&JImmuno_BioManagedStatusAKPharm

Aetna, Inc.

Covered Account

Managed Medicaid

Bio Managed 2

Remicade

201903

Immunology

582953

1

663485

J&JImmuno_BioManagedStatusAKPharm

Aetna, Inc.

Covered Account

Managed Medicaid

Clinical Criteria Required

Remicade

201903

Immunology

30023

3

663485

J&JImmuno_BioManagedStatusAKPharm

Aetna, Inc.

Covered Account

Medicare

Clinical Criteria Required

Remicade

201903

Immunology

3506870

1

3512113

J&JImmuno_BioManagedStatusAKPharm

Aetna, Inc.

Covered Account

Medicare

PA or ST Details Unknown

Remicade

201903

Immunology

5243

2

3512113