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 @cristina1128,
Could you please mark the proper answers as solutions?
Best Regards,
Dale
hello, neither answer was correct. after much research i found the correct formula which is -
dax % ttl:
=SUMX(tblName, tblName[columnName])/SUMX(ALL([columnName]), tblName[columnName])
Hi,
This is an alternative
=SUM(tblName[columnName])/CALCULATE(SUM(tblName[columnName]),ALL([columnName]))
Hope this helps.
If memory serves you had more than 2 years worth of data represented in that table. wont this solution mean you need to write a year diff % each time you start a new year? The aim should be to make tall tables not wide tables. this gives you the ability to write just the one measure and it will work on all years.
I think you might need to reformat the data in powerquery to do this a bit more effectively. Right click the elipses to the right of the CUST_RECAP table and edit query. Once in the query editor, you will want to:
You should now have a table with xCAT, yCatName, Year, xTTL $, xTTL U, ySHIP $, ySHIPU, zOPPK $, zOPPK U
Save and apply changes.
Now write some measures:
1. TTL XCAT$ =SUM(CUST_RECAP[xCAT $)
2. TTL XCATU=SUM(CUST_RECAP[xCAT U)
etc
etc
etc
To get the % per category one write something like:
=CALCULATE([TTL XCAT$],ALLEXCEPT(CUST_RECAP,yCAT)) / [TTL XCAT$]
To get the % change one write something like:
=( [TTL XCAT$] - CALCULATE([TTL XCAT$],DATEADD(CUST_RECAP[Year],-1,YEAR))) /
CALCULATE([TTL XCAT$],DATEADD(CUST_RECAP[Year],-1,YEAR))
I would reccomend having a bit of a read of some of the resources on this site to find out how to make the calendar tables since they really are key to the year on year calculations. Also might be worthwhile having a look to see if there is a local PUG group you could go and visit. There could be some people with some good ideas about how to structure your data models in the best ways.
Hope it all works out.
Cheers,
Sam
// If this is a solution please mark as such
Hi,
So my understanding, you could just create 2 new columns on the query editor.
Query Editor -> Add Column -> Custom Column , then you could change the column name and do the calculation there.
Hope it helps.
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 |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |