# SUM based on Distinct Values of Another Field

• ### Question

• Hi All,

I am seeking one help to write a formula to calculate values (below is the example)

Currency    Product     Amount
USD            Bike        100
USD            Cycle        50
USD            Brakes       10
EUR            Bike           75
EUR            Cycle         35
AUD            Bike         115
AUD            Cycle         70

I want to visualise this data like:

Currency        Total Sales
USD                160
EUR                110
AUD                185

Product   USD    EUR       AUD
Bike        100     75        115
Cycle       50      35         70
Brakes     10        0          0

I calculated Distinct Currency in a measure and then tried to perform sum using below command but its not recognising my measure.

Currencys:=DISTINCT([Currency])

Measure 1:=CALCULATE(SUM([Amount]), ALL(Currencys))

Regards

• Edited by Tuesday, December 6, 2016 10:19 PM modified code
Tuesday, December 6, 2016 9:55 PM

• Hi GURSETHI,

A measure is a numeric calculation used in data analysis. Examples that are commonly found in business reports include sums, averages, minimum or maximum values, counts, or more advanced calculations that you create using a DAX formula. The DISTINCT function is not a Clustering function. And the measure1 will calculate the total sum of all Currencys. You should add filter in the measure for Currencys and Product.

I try to reproduce your scenario using your given sample data, create two measures using the formulas below.

`Currency total:=CALCULATE(SUM(Table1[Amount]),ALLEXCEPT(Table1,Table1[Currency]))`

`Product total:=CALCULATE(SUM(Table1[Amount]),ALLEXCEPT(Table1,Table1[Product]))`

Then create two pivot table, the first pivot table, select the Currency field as Row level, the measure(Currency total) as Value level, you will get desired result, please review the following screenshot.

Create another pivot table, select the Product field as Row level, the measure(Product total) as Value level, you will get another table below.

If you have any other question, please let me know.

Best Regards,
Angelia

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

Wednesday, December 7, 2016 2:05 AM