Forum Discussion
help with calculated columns
im running into an issue trying to get this calculated column to properly display return unique values from a column on another table
the formula in this column of this table just counts the rows (of a different table) and returns the total count for certain dates when the filter criteria is met, the problem im having is i want this formula to be able to return a total sum of the values within the rows of a specific column but im not sure which formula i can use to do that. Any help would be greatly appreciated.
Thank you for taking the time to try an anwser my confusing question, i actually ended up figuring out what the problem was basically in the formula instead of the countrows function i needed to use a sum function instead like so:
MWET_Act =CALCULATE(sum('EXPORT - HVAC WET (PJM)'[length]),FILTER('EXPORT - HVAC WET (PJM)', 'EXPORT - HVAC WET (PJM)'[install date] = 'MWET Install'[Value]))the only reason I kept getting errors was because the column that had the length data that I wanted to sum was being imported as text instead of numbers, asoon as i made that change to the imported length data and catagorized it as decimal numbers the formula i wrote worked.
7 Replies
Hi ye348
Ha ha you have told us what is not working rather than provide a clear description of what you need.
A common mistake on this forum 😀😀😀
We want to help.
Please provide examples of your input tables (not screem shots)
and examples of the desired output with a clear description of what you want.
It sounds like you need to use ALLEXCEPT in the FILTER but you have not given enough infomation yet.
Thanks
whoops my bad posted my reply in the wrong area, ok so for input i have a column from a table i created (lets call it "Table 2") and it has the following formula
FPS_Act =CALCULATE(COUNTROWS('EXPORT - FPS (RSP)'),FILTER('EXPORT - FPS (RSP)', 'EXPORT - FPS (RSP)'[install date] = 'FPS Install'[Value]))table 1 LENGTH INSTALL DATE 4 26-Apr 4.7 21-Apr 5.2 2-May and its basically just returning counts of rows from a different table ("Table 1") when the filter critera "FPS (RSP)'[install date] = 'FPS Install'[Value]" is met.this works perfectly for other tables i have because the value in each row its counting is just 1 so a simple count is fine but in table 2 its different because the values in that length column vary (1,4.2,6.9, etc...), so a simple row count wont work.The result im trying to get would be to have a formula that returns the sum of the values in the rows instead of just a simple count of the rowsalso just to clarify in this example 'EXPORT - FPS (RSP)' is "TABLE 1"please let me know if that makes sense or if i need to give more info
whoops my bad, ok so for input i have a column from a table i created (lets call it "Table 2") and it has the following formula
FPS_Act =CALCULATE(COUNTROWS('EXPORT - FPS (RSP)'),FILTER('EXPORT - FPS (RSP)', 'EXPORT - FPS (RSP)'[install date] = 'FPS Install'[Value]))table 1 LENGTH INSTALL DATE 4 26-Apr 4.7 21-Apr 5.2 2-May and its basically just returning counts of rows from a different table ("Table 1") when the filter critera "FPS (RSP)'[install date] = 'FPS Install'[Value]" is met.this works perfectly for other tables i have because the value in each row its counting is just 1 so a simple count is fine but in table 2 its different because the values in that length column vary (1,4.2,6.9, etc...), so a simple row count wont work.The result im trying to get would be to have a formula that returns the sum of the values in the rows instead of just a simple count of the rowsplease let me know if that makes sense or if i need to give more infoi should also mention that in the formula 'EXPORT - FPS (RSP)' is "TABLE 1" i just forgot to change that name for the example i just posted
Hi ye348 ,
Not very clear.
I have built a data sample and use your DAX to create FPS_Act column in Table2.
If you are still confused about it, please provide me with more details about your table and your expected output or share me with your pbix file after removing sensitive data.
Refer to:
How to Get Your Question Answered Quickly - Microsoft Power BI Community
How to provide sample data in the Power BI Forum - Microsoft Power BI Community
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Thank you for taking the time to try an anwser my confusing question, i actually ended up figuring out what the problem was basically in the formula instead of the countrows function i needed to use a sum function instead like so:
MWET_Act =CALCULATE(sum('EXPORT - HVAC WET (PJM)'[length]),FILTER('EXPORT - HVAC WET (PJM)', 'EXPORT - HVAC WET (PJM)'[install date] = 'MWET Install'[Value]))the only reason I kept getting errors was because the column that had the length data that I wanted to sum was being imported as text instead of numbers, asoon as i made that change to the imported length data and catagorized it as decimal numbers the formula i wrote worked.
Hi ye348 ,
Got it. So it seems that your issue has been solved by yourself, right? Please kindly Accept it as the solution. More people will benefit from it.
Best Regards,
Eyelyn Qin
