Sum of values on a related filtered table

[karun_r]

I have a simple question in DAX.

Let's say if I have a Revenue table with customers and the revenue generated by them for each day in a quarter.

Like

Customer      Date     Revenue

A                    01/01    23

B                    01/01    24

C                    01/01    25

.

.

.

.

.

.A                   03/31   45

B                    03/31   32

C                    03/31   27

And I have a customers table which will hold only the unique names of the customers in a column. And I also have a relationship between the customers table and revenue table on the customers column.

If I have a date slicer in my report, how do I create a calc column in this customers table, that will show the total revenue of the corresponding customer for the dates provided by the data slicer ?

I tried CALCULATE(SUM(Revenue[Revenue])) which is the basic formula, but it gives me the total revenue instead of just the revenue for the selected dates. How do I fix this ? I am sure that I am missing something very simple.

[Phil_Seamark]

Which date field are you using in your date slicer?  The Date field from the revenue table?  or from another table?

[karun_r]

From the same Revenue table.

[Phil_Seamark]

In theory that should be enough.  The slicer will just restrict the rows from the Revenue table that are visible to the SUM function. 

[karun_r]

But that is not happening as expected. It is still returning the sum of all revenue from all dates instead of just the selected dates. I wonder why this is happening to all my date slicers

[karun_r]

Same thing is happening with filters on other columns as well. If I have column called Category, and if I use a filter of Category in my report, it is not having any effect on the revenue column in my customers table. Whereas it does affect the revenue column in the revenue table.

[Phil_Seamark]

Hi @karun_r

So there are no other columns called 'Revenue' in any other table in your model?

[karun_r]

No, there is only one column with Revenue as it's name and it is the one that I created

[Phil_Seamark]

You haven't inadvertanly used the "edit interactions" feature to disable filtering of your visual by the slicer?

[Phil_Seamark]

Oh I see.

Try a calculated measure like this..

Measure = CALCULATE(SUM('Customer'[Revenue]),RELATEDTABLE(Customer))

[TomMartens]

Hey, can you please explain why you created the calculation in your customer table?

The "normal" way is to use columns from different tables in the same table, but I also have to admit, that I do not know what is "normal" for sure

[karun_r]

The reason for creating a seperate column is that, in my base table, I have granularity on date level. I needed an aggregate view of the revenue for each customer and I should be able to assign a range based on the revenue generated by them. 

If I do all this in my base table, I need to go doing it like this:

• Create a measure that will hold the total revenue of the customer for the dates selected -- Working
  • Define a measure to assign a range to the customer -- Working
    • Aggregate the range divisions to show how many customers fall under each range -- Not working as I am using a measure for range and it is not possible to aggregate on a measure
  • Define a calc column to assign a range to the customer -- Not Working as it is considering the dates and assigning a range based on the revenue on one single date instead of aggregated revenue
•    Create a calc column that will hold the total revenue of the customers for the dates selected -- Not working as it is considering the dates  on the row context instead of aggregated revenue

In an attempt to fix all this, I am trying to create a custom table that will store the aggregated values which will change automatically as the date slicer is adjusted.

[TomMartens]

Hm,

"Aggregate a Measure ... not working"

This is not a fundamental law :-), just not that obvious and can become very mindboggling

You can do something like

1. Create a Measure

2 Create a 2nd measure that uses a pattern like this

Measure 2 =
SUMX(
    ADDCOLUMN(
        SUMMARIZE(
        
        )
       ,"A funny name", [Measure 1]
    )
    , Do something with the column "A funny name" in an if statement
)

Consider to create sample data, upload a pbix to onedrive and share the link. This sounds like an interesting challenge. Also consider the new DAX formula SUMMARIZECOLUMNS, this formula is described here

https://www.sqlbi.com/articles/introducing-summarizecolumns/

Regards

Tom

Regards

Tom

[karun_r]

@TomMartensI am not sure if I follow the pattern mentioned by you for the second measure.

I actually tried creating a different column based on the measure using the VALUE function earlier but it did not work.  I used the below formula format.

Range as CalcColumn = VALUE([Range as Measure])

And for the sample file, I had a question about this yesterday and I've uploaded the file here in an another question

https://1drv.ms/u/s!AmE9ILAWJzWtgf9JbHAH0Oh_z3MIOw

Link to that discussion I had with @Phil_Seamark yesterday

https://community.powerbi.com/t5/Desktop/ISFILTERED-Usage/m-p/381985

[karun_r]

Measure will work. But I need a calc column for other purposes. I need to be able to assign a range for this revenue column for each company. And if I use a measure, I ran into other issues earlier where in the range was not being assigned properly

[Phil_Seamark]

A calculated column would use the same syntax.

But values generated by calculated columns are static and cannot be modified by a filter.  Filters will only control which rows are visible to DAX functions.

New Column in Revenue table = CALCULATE(SUM('Customer'[Revenue]),RELATEDTABLE(Customer))

[TomMartens]

A calculated column will never "adjust" to selections from a slicer, this is a fundamental law in the data model of Power BI and also of SSAS Tabular.

So it seems you have to think outside the box

[karun_r]

No, AFAIK, I did not disable any interactions from visuals. Even then, if that is case, the slicing should not work when I use the actual column with the customer name in my "Customer" table, isn't it ?

[TomMartens]

Hey,

I'm wondering what will happen, if you create a table visual that contains the customer column form the customer table and the revenue column from your revenue table, not the measure you calculated.

Basically the above mentioned should just provide what you are looking for, this is odd

And can you also pleases make a screenshot from  your table relationships pane

Regards

Tom

[karun_r]

I actually tried that way. It works as expected when I pull customer name from my Customer table and Revenue from Revenue table. But the custom calc column that I created doesn't behave in the same way.

Attached the relationship.