Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
karun_r
Microsoft Employee
Microsoft Employee

Sum of values on a related filtered table

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.

 

19 REPLIES 19
Phil_Seamark
Microsoft Employee
Microsoft Employee

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


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

From the same Revenue table.

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

 

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

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
Microsoft Employee
Microsoft Employee

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.

Hi @karun_r

 

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


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

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

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


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Oh I see.

 

Try a calculated measure like this..

 

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

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

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.

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

@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

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

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))

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

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 ?

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

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.

 

Relationship.PNG

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.