Forum Discussion
Measure aggregation
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.
Hi, @nickbeltran7
Based on the information you gave, I have created two tablesCreate 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.
4 Replies
nickbeltran7
Not sure how you have set up your model, please share an image of your model.Hi, @nickbeltran7
Based on the information you gave, I have created two tablesCreate 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.
Indeed It did work. Thank you so much!
