Forum Discussion
Total is wrong
Hi,
Create the following measures
F12 total = SUM('NCC Chart''s'[F12])
H12 total = SUM('NCC Chart''s'[H12])
Total_FC=SUMX(SUMMARIZE(VALUES('NCC Chart''s'[Project_Number]),[Project_Number],"ABCD",average('NCC Chart''s'[Net_NCC_Cost])+IF(H12-F12>0,-(H12-F12)*1000000,(-H12+F12)*1000000)),[ABCD])
In your visual drag replace H12, F12 and Total_FC with the measures created above.
Hope thsi helps.
Dear all, sorry to enter your discussion but it is really a KEY point for my project and I am trying to solve it since many days with no success.
My situation is exactely the same, looking at the results, but different about the origin of the data.
Please try to help me!!!!! (Let me know if I should write a new post....)
I think I understood the solutions offered using SUMX, but it seems referred to data that are in a table.
I.E. if I have I table like the following one:
DATE value1 value2
01 jan 18 10 15
02 jan 18 5 1
03 jan 18 7 2
In this case, if I should choose between "value1" and "value2" under certain conditions, it seems possible to use SUMX with an IF statement ore HASONEVALUE and so on. (Reading aroung on theweb it seems to be the most used solution)
But MY situation is that I have 2 tables (from a database) from which to take the information, and managed through measures.
Like followings:
FORECAST:
DEFINITE:
So the values I have to show are not originated from values from one table, but they are measures originated from 2 tables . (Actually it is a simplification... in reality they are measures coming from other measures filtered by other elements...but all of them from 2 original tables)
They are:
OTB FORECAST = SUM(FORECAST[ITEMS])
OTB DEFINITE = SUM(DEFINTE[ITEMS])
My needed results are also depending to a "reading date":
for a certain date ( i.e. 2nd jan) if the reading date is later than the date we are looking at, then show "DEFINITE", otherwise show "FORECAST":
ACTUAL = IF([maxreadingdate]<=[maxdate];[OTB FORECAST];[OTB DEFINITE])
You can see from the image below it is ok, except than for the month total that, for ACTUAL, should be 673. The presence of the "maxdate" and "mareadingdate" on the "month total" row demonstrate the way PowerBI manage this situation:
on the "month total" row the IF statement is satisfied, therefore it makes the calculation on the "OTB FORECAST" values.
About SUMX: I have seen that SUMX applies to TABLES. But as said my [OTB FORECAST] and [OTB DEFINITE] are not in a table... they are measures...
It seems I cannot use it!
Also the "HASONEVALUE" cannot be applied...
AAARRRGHHH !!!!! I REALLY REALLY NEED HELP!!! Feeling really frustrated.
Thank you guys!
Alessandro.
Hi all it's me again, if you like to help here you can find the pbix file and datas....
https://1drv.ms/f/s!Ar-kpjLj8CF4jFsje0NU4qE0VmwH
Thank you again.
Alessandro
GREAT !!!! Thank you so much Ashish_Mathur, it works!!!!!
Really the biggest thank you!
I would never had reached the solution by myself. I will study it deeply.
Now... I realized I cannot markl this post as "solved" since the original post is not mine... am I correct?
Is there anyway for me to confirm in this community that you solved my problem ?
Alessandro.