Forum Discussion

Cotter2000's avatar
Cotter2000
Frequent Visitor
9 years ago

Revenue by Customer & Year, Filtered Dynamically by additional field

Hi everyone,   New to Dax here.  I have four fields:   Customer, Product Format, Price, Transaction Year   For each customer AND transaction Year, I'm trying to calculate a sum of Price for any...
Need Help
Anonymous's avatar
Anonymous
Not applicable
9 years ago

Cotter2000,

Directly create your measures using the following DAX, then check if you get expected result.

RevenueSum = SUM('Purchasers'[Price])
Revenue Bucket = IF([RevenueSum]<.01,"1. $0",IF([RevenueSum]<=25,"2. $1-$25",IF([RevenueSum]<=50,"3. $26-$50",IF([RevenueSum]<=75,"4. $51-$75",IF([RevenueSum]<=100,"5. $76-$100",IF([RevenueSum]<125,"6. $101-$124","7. $125+"))))))


Regards,
Lydia Zhang

Cotter2000's avatar
Cotter2000
Frequent Visitor
9 years ago

Anonymous

Hi Lydia,

 

Thanks for your response. 

 

That is actually where I had started.  Your equation matches my first attempt.  Since it is dynamically summing up Price with no other filters placed on it, it simply copies the revenue from each Product Format/Year/Customer and it assigns it to that Bucket. 

 

CustomerYearFormatRevenueRevenueSumRevenue Bucket
12015Format A1301307. >$125
12015Format B4242.43. $26-$50
12015Format B53534. $51-$75
12015Format C1001005. $76-$100

 

I'm trying to get the Sum to fluctuate as I pick different product formats, but never fluctuate beyond the boundaries of Year and Customer.  If I looked at 10 customers, and I isolated different product formats, it would assign each customer one and only one Revenue Bucket, based on only the Product Formats I've isolated. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Cotter2000,

    Would you mind sharing me all the raw data of your table and post expected result here?

    Regards,
    Lydia Zhang

    • Cotter2000's avatar
      Cotter2000
      Frequent Visitor
      9 years ago

      Hi Anonymous,

       

      I can't send actual data, but I have faked some data to show you what I am after.  Here is the table:

       

      CustomerYearFormatRevenue
      12015A130
      12015B42
      12015B53
      12015C100
      12016A200
      22015A20
      22015D30
      22015E50
      22016D50
      32015A100
      32015A200
      32015B50
      32016B60
      32016B80

       

      I want to sum revenue and count people by revenue bucket.  I add the following two fields to Power BI:

       

      RevenueSum = CALCULATE(SUM(Sheet1[Revenue]),ALLEXCEPT(Sheet1,Sheet1[Customer],Sheet1[Year]))

       

      Revenue Bucket = IF([RevenueSum]<.01,"1. $0",IF([RevenueSum]<=25,"2. $1-$25",IF([RevenueSum]<=50,"3. $26-$50",IF([RevenueSum]<=75,"4. $51-$75",IF([RevenueSum]<=100,"5. $76-$100",IF([RevenueSum]<124,"6. $101-$125","7. >$125"))))))

       

      I then can use the buckets to show revenue and counts of unique customers.  But when I then further filter on Product Format, the amount of revenue does not change accordingly.  

       

      Correct - prior to format filterFormat Filter applied, no longer correct

       

      When I format for only Product Format B, it should show me no revenue, because no customer in 2015 on their own has $125+ revenue within Format B. 

       

      Let me know what you think - thanks!