Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I am trying to summarize a table across Regions and Categories for the amont of Spend, but then I also want to add another column to the grouping for the 'Asset' which comes from a table linked to my 'Visibility Data - All Regions' table.
How would I best go about doing this? I was hoping I could somehow use the RELATED function but having no luck.
Below is my current table without Asset added in.
SUMMARIZE('Visibility Data - All Regions', 'Visibility Data - All Regions'[Region], 'Visibility Data - All Regions'[Group], 'Visibility Data - All Regions'[Category], 'Visibility Data - All Regions'[Sub Category], "Revised Spend (USD)", sum('Visibility Data - All Regions'[Revised Spend (USD)]) )
Thanks,
TG
Solved! Go to Solution.
@Anonymous
If that other table with Asset column is on the side of the relationship, then you can simply add it as GROUPING Column
= SUMMARIZE ( 'Visibility Data - All Regions', 'Visibility Data - All Regions'[Region], 'Visibility Data - All Regions'[Group], 'Visibility Data - All Regions'[Category], 'Visibility Data - All Regions'[Sub Category], OtherTableName[AssetColumn], "Revised Spend (USD)", SUM ( 'Visibility Data - All Regions'[Revised Spend (USD)] ) )
@Anonymous
If that other table with Asset column is on the side of the relationship, then you can simply add it as GROUPING Column
= SUMMARIZE ( 'Visibility Data - All Regions', 'Visibility Data - All Regions'[Region], 'Visibility Data - All Regions'[Group], 'Visibility Data - All Regions'[Category], 'Visibility Data - All Regions'[Sub Category], OtherTableName[AssetColumn], "Revised Spend (USD)", SUM ( 'Visibility Data - All Regions'[Revised Spend (USD)] ) )
Hi Zubair,
Thanks for the quick response but I tried that and I get the issue "The column 'Facility / Asset Name' specified in the 'SUMMARIZE' function was not found in the input table."
FYI - the data already being summarized is linked to the table containing Facility / Asset Name but on another column.
Any ideas?
TG
@Anonymous
Could you share your file?
Hi Zubair,
Afraid I cannot as the data is very sensitive client data.
I have a main fact table containing the Region, Category, and Spend, as well as Cost Centre, I then have a seprate mapping table to map the Cost Centres to Assets. The same aggregation works fine in a front end chart not sure why I am getting this error when doing it in DAX.
Thanks,
TG
Hi @Anonymous,
Is the column in the ONE side of the relationship? If so, you can add it in summarize without any errors. Can you share a DUMMY sample that can reproduce the issue?
Best Regards,
Dale
Hi @v-jiascu-msft,
Ah, I actually cleaned up my data model yesterday as I was previously using the new Many-to-Many relationship option for just one of the tables (the one I needed to get the extra column from!).
Trying the same code @Zubair_Muhammad suggested yeterday has worked perfectly! Is there a reason why this won't work on the MANY side?
Thank you both for you help.
TG
Hi @Anonymous,
As we can see from the definition on this site, SUMMARIZE returns totals over a set of groups. So what can we expect the totals of one-side from the many-side?
I'm glad you solved the issue.
Best Regards,
Dale
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
97 | |
80 | |
69 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |