Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
Is there a way to show the total sum of another measure that's using SUMX?
For each row the values are correct but the total is wrong for the field called Impact.
Impact shows: 32, 527 (Wrong)
Impact total should be: 32, 473
I want the total to show the sum of all values above total of Impact so in this case (1,900+15,440+1,800+13,333)
The user can also slice on Date, Supplier and Item so the total should be dynamic.
Measures used:
Comp:
SUMX('fact';CALCULATE(DIVIDE(SUM('fact'[SumGLAmount]);SUM('fact'[SumGL/PO Qty]))))
Base:
CALCULATE(SUMX('fact';CALCULATE(DIVIDE(SUM('fact'[SumGLAmount]);SUM('fact'[SumGL/PO Qty]))));SAMEPERIODLASTYEAR('Date'[Date]))
Weighted C:
SUM('fact'[WeightedPMI])
Weighted B:
CALCULATE(SUM(fact[WeightedPMI]);SAMEPERIODLASTYEAR('Date'[Date]))
Weighted Change CY vs LY %:
DIVIDE(([Weighted C]-[Weighted B]);[Weighted B])
Ongoing:
DIVIDE(([Comp]-[Base]);[Base])
Impact:
IF(HASONEFILTER('vtotalcost'[Item RM]);
[Base]*(1+[Weighted Change CY vs LY %]);
SUMX(VALUES('vtotalcost'[Item RM]);
[Base]*(1+[Weighted Change CY vs LY %]))
)
hi, @Kinesaren
Total result is calculate base on the whole table
for example:
basic data and expected result
the create measure
Measure = DIVIDE(CALCULATE(SUM(Table1[Qty])),CALCULATE(SUM(Table1[week])))
Normal result:
total is (10+12+15)/(2+6+15)=1.61 not 5+2+1=8
then you need to use this measure and basic table to create a temporary dynamic table and use SUMX to create a measure like below:
Measure 2 = var _table=SUMMARIZE(Table1,Table1[ID],"_per",DIVIDE(CALCULATE(SUM(Table1[Qty])),CALCULATE(SUM(Table1[week]))) ) return SUMX(_table,[_per])
IF not your case, Please share some data sample and the formula and expected output. You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading
Best Regards,
Lin
Hello @v-lili6-msft
Thank you for answering.
However, the value 'impact' is already a calculated measure and when I'm trying to use it in the 'Measure 2' statement with the summarize syntax it will only return blank results.
I have provided the sample file here:
https://www.dropbox.com/sh/9lpz5uo41xhpyl8/AABJLREFUZz_6PrUe8rkT9f9a?dl=0
The expected output should be 32, 473 (Sum of all single 'Impact' values)
I will try to experiment with the temporary dynamic table approach.
Thank you!
This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376
Now, the general fix for this that I use is, and I'm making a few assumptions here:
Impact Single = [Base]*(1+[Weighted Change CY vs LY %]); Impact = IF(HASONEVALUE('vtotalcost'[Item RM]); [Impact Single] SUMX( SUMMARIZE('vtotalcost',[Supplier Number],[Item Number],[Item RM],"__Impact",[Impact Single]), [__Impact] ) )
So, essentially, write your measure to work for a single row in a table or matrix. Then, write another measure that uses that measure for single rows but essentially recreates the table/matrix in memory (using your measure for single rows) and then do a SUMX across that table.
Hello @Greg_Deckler
Thank you for answering!
Your measure 'Impact' is returning an incomplete statement but I understand what you are trying to achieve.
I will continue to try with the tempory dynamic table approach.
Thank you!
Sorry, missed a comma. Check out my newly created Quick Measure, Measure Totals, The Final Word:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907
Impact Single = [Base]*(1+[Weighted Change CY vs LY %]) Impact = IF(HASONEVALUE('vtotalcost'[Item RM]); [Impact Single]; SUMX( SUMMARIZE('vtotalcost',[Supplier Number],[Item Number],[Item RM],"__Impact",[Impact Single]); [__Impact] ) )
Hello @Greg_Deckler
Your article was very helpful in understanding the problem I am having, so thank you for that.
However, I am still having problem on a total level and I suspect it is something with the Time Intelligence functions that I am using. (The single measure is using CALCULATE together with SAMEPERIODLASTYEAR and a date table.
If I remove the measures where I have Time Intelligence DAX involved then the total level is all good but I am too dependent on those.
I am having exactly the same issue. @Kinesaren, have you been able to resolve this?
@Kinesaren Please post the same sample data that can be copied
Proud to be a PBI Community Champion
Hello @PattemManohar
Thank you for answering!
I have provided the sample file here:
https://www.dropbox.com/sh/9lpz5uo41xhpyl8/AABJLREFUZz_6PrUe8rkT9f9a?dl=0
User | Count |
---|---|
130 | |
108 | |
101 | |
67 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |