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.
I have two tables I'm using in a formula - one is an Actuals table and one is a forecast table. My formula basically says if Actuals are larger than forecast, use Actuals if not use the forecast value. This works for all the individual rows when I look at specific programs but the grand total row is not the sum of all the individual programs. I tried adding a SUMX but I must be doing something wrong. Here's my formula:
CQ Factored ACT = SUMX( 'ACT DATA' , ( IF ( 'ACT DATA' [value] > 'FCST DATA' [value], 'ACT DATA' [value] , 'FCST DATA [value]))
So, what I want to see in my grand total is the $1,800 (the total of all the individual programs) but what I get is the $1,750 since the total of FCST is larger than the total of ACT.
ACT | FCST | Factored | |
Program 1 | 50 | 100 | 100 |
Program 2 | 900 | 850 | 900 |
Program 3 | 650 | 800 | 800 |
1,600 | 1,750 | 1,800 |
Solved! Go to Solution.
Hi @Anonymous
Then just change the measure to:
Total_Projects = SUMX ( ADDCOLUMNS ( Project;"ACT"; RELATED(ACT[ACT]; "FCT"; RELATED ( FRCST[FCST ] ) ); IF ( [ACT] > [FCT]; [ACT]; [FCT] ) )
Should work in the same way but you have the connection table as a starting point.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi,
Share the link from where i can download your PBI file.
If both your forcast and actual are linked then at the linked table you can create a calculated column that uses an IF to assinged a value to that column using your comparison logic
Then a sum of that will give you a sum of that result and since this is assinged per project then you can slice that by program.
However i am assuming that there is a 1:1 relationship between both ProjectID and Actual and ProjectID and Forecast.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsI agree that it does contribute in some manner to the overall size of your file. Given the number of rows this contribution can be insignificant to immense.
Cheers Mate !
Hi @Anonymous,
I'm assuming your two tables are related by the Program column and that this as a relation of one to one. Create the following measure:
Total_Projects = SUMX ( ADDCOLUMNS ( Actuals; "FCT"; RELATED ( FRCST[FCST ] ) ); IF ( [ACT] > [FCT]; [ACT]; [FCT] ) )
The SUMX depends on a table to return the result of the sum since you have values on two tables actuals and forecast the result is not as expected.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsSorry, I was trying to simplify my explanation by saying only two tables. The ACT and FCST tables both have many rows for each project. There is a third table that they are both linked to that has the unique project ID. They are all connected by the Project ID.
Hi @Anonymous
Then just change the measure to:
Total_Projects = SUMX ( ADDCOLUMNS ( Project;"ACT"; RELATED(ACT[ACT]; "FCT"; RELATED ( FRCST[FCST ] ) ); IF ( [ACT] > [FCT]; [ACT]; [FCT] ) )
Should work in the same way but you have the connection table as a starting point.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCovering 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 |
---|---|
98 | |
97 | |
81 | |
77 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |