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.
Level 2 | Level 1 | Final Amount | |
7353 | 100208 | 38.58 | |
7352 | 100289 | 128.2 | |
7352 | 100293 | 61.08 | |
7353 | 100293 | 70.31 | |
008358-MARS | 100293 | 83.59 | |
7352 | 100331 | 9.05 | |
7730 | 100331 | 10.6 | |
7355 | 100331 | 2.93 | |
7353 | 100331 | 10.02 | |
7356 | 100331 | 2.92 |
I am trying to sum the final amount based on the level 1 column and the current way is only summing based on level 2.
So when I put it in my report the Final Amount Tool Tip and the # Total Final Amount show the same number instead of the latter showing the aggregate. What am I missing? I have also tried Sumx and it does the same thing.
Hi @SurfingData ,
You could use the below to create a new column :
sum =
CALCULATE(SUM('Table'[Final Amount]),
FILTER(ALL('Table'),'Table'[Level 1]=EARLIER('Table'[Level 1])))
And if you want to create a measure to get the sum,use the below:
sum2 = CALCULATE(SUM('Table'[Final Amount]),FILTER(ALL('Table'),'Table'[Level 1]=MAX('Table'[Level 1])))
Best Regards
Lucien
Level 3 | Level 2 | Level 1 | Final Amount | |
123 | 7353 | 100208 | 38.58 | |
123 | 7352 | 100289 | 128.2 | |
123 | 7352 | 100293 | 61.08 | |
123 | 7353 | 100293 | 70.31 | |
123 | 008358-MARS | 100293 | 83.59 | |
123 | 7352 | 100331 | 9.05 | |
123 | 7730 | 100331 | 10.6 | |
123 | 7355 | 100331 | 2.93 | |
123 | 7353 | 100331 | 10.02 | |
123 | 7356 | 100331 | 2.92 |
@v-luwang-msftLucien, Thank you so much for taking the time to help me.
I mis stated my problem statement. Your solution is great but aggregating the entire table( I gave only a section of my data to illustrate the problem). So all the isntances of 100331 are totalling, which is what I originally stated I needed.
After using your solution I realized it was not actually what I needed to total by:
I need to aggregate all the level 1 numbers by a level 3 column. I tried to modify your solution to :
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 |
---|---|
113 | |
97 | |
84 | |
67 | |
60 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |