locked
SUM based on Distinct Values of Another Field RRS feed

  • 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


    Regards Gursethi Blog: http://gursethi.blogspot.com/ ++++ Please mark "Propose As Answer" if my answer helped ++++


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

Answers

  • 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