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.
This is for excel 2016, not really power bi, but let's face it. Mrexcel is a ghost town for power pivot
anyway I have a table a
And a table that I need to aggregate table b
That I need to aggregate grouped on d2 (Table b aggregate):
Then I want to join table b aggregate with table 1 so I can get this pivottable
I bet there's a super complex nested groupby dax formula to do this, but I can't figure it out. How would you even step your way to the full formula by testing each nest unless you use dax studio?
Hi @Ffitzpatrick47,
I'm not sure how to achieve these with dax excel version, but these operation is possible to achieve in power bi.
Sample table formula:
b aggregate = SUMMARIZE ( FILTER ( b, [d4] IN VALUES ( a[d3] ) ), [d2], [d4], "Percent", DIVIDE ( CALCULATE ( SUM ( b[value] ), ALL ( b ), VALUES ( b[d2] ), VALUES ( b[d4] ) ), CALCULATE ( SUM ( b[value] ), ALL ( b ), VALUES ( b[d2] ) ), -1 ) ) join Table = ADDCOLUMNS(a,"Percent",[measure]*LOOKUPVALUE('b aggregate'[Percent],'b aggregate'[d2],a[d2],'b aggregate'[d4],a[d3]))
Regards,
Xiaoxin Sheng
There's a myth that excel 2016 handles variables, but I don't even have the simplest code to test it. I can break up one of my successful calculate equations like
var f = filter(all(x[a],[a]=1)
calculate(sum(x[b]),f) but I don't see it working. It just seems too good to be true
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 |
---|---|
114 | |
100 | |
81 | |
70 | |
62 |
User | Count |
---|---|
148 | |
116 | |
104 | |
90 | |
65 |