topic Re: Measure aggregation in DAX Commands and Tips

Measure aggregation

nickbeltran7 — Fri, 19 Jan 2024 17:09:57 GMT

I have the following measure in PowerBI: SPACES = (SUM('Properties'[Spaces]'). This measure aims to return the total number of spaces each Id has. Additionally, I have two other columns in two different tables. One column is called "Id" and the other column is "Month" in a time dimension table. When I pull a matrix with Id in rows, and Month in columns, and SPACES as values, it performs an aggregation rowwise and columnwise, which is what I want. However, when the aggregation is done rowwise at the asset level, the aggregation returns the SPACES in one month instead of the sum of SPACES each month. Note: Even though each month has the same number of spaces, I still want it to sum up the spaces of the twelve months. I am attaching an image of the matrix so you can understand me better. For example, for ID 302, I want the total to be 1,500, not 125. If anyone could provide any sort of guidance on this, I would appreciate it.

Re: Measure aggregation

Fowmy — Fri, 19 Jan 2024 17:29:00 GMT

nickbeltran7

Not sure how you have set up your model, please share an image of your model.

Re: Measure aggregation

Anonymous — Mon, 22 Jan 2024 08:27:33 GMT

Hi, @nickbeltran7

Based on the information you gave, I have created two tables

Create a New Measure named SPACE.

I'm trying to use your measure and follow your way, and then here's my idea: create another measure, create a virtual table in it, create a virtual table with the id and Month columns using the Cartesian product, and add a new column to this virtual table with [SPACE] as the value. SUMX is then used to calculate the sum of each row. The DAX expression is as follows:

Measure =

VAR a = ADDCOLUMNS(CROSSJOIN(VALUES('Table'[ID]),VALUES('Properties'[Month])),"Me",[SPACE])

RETURN

SUMX(FILTER(a,[ID] in VALUES('Table'[ID])&&[Month] IN VALUES('Properties'[Month])),[Me])

Here's my rendering:

I hope my ideas can help you.

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Measure aggregation

nickbeltran7 — Mon, 22 Jan 2024 15:45:34 GMT

Indeed It did work. Thank you so much!

Re: Measure aggregation

Anonymous — Tue, 23 Jan 2024 03:15:28 GMT

Hi, nickbeltran7
If the solution works, can you accept it as a solution?

Best Regards

Yongkang Hua