Forum Discussion
Min and Max Value for a Row with Multiple Columns
Hello,
I'm a PowerB beginner and have the attached a screen shot of a table in Power BI that is called 'Raw Data (2)'. I've searched through multiple posts and played around with some formulas that others have used (where I've updated with my tables column names) but I haven't been able to get them to work, they come up with errors, or it's just not puplating a value in the field. Does anyone know what either the Dax or Custom Column formula should be to calculate a 'Min' column and a 'Max' column specifically for a Financial Year which for me is July 2023 to June 2024 (noting I don't have all of theose months in my table, they're just cut off in the photo)? Any help would be greatly appreciated. Thanks very much!
Hi NMSJGHC ,
Here are the steps you can follow：
1. Select the month year column– Transform – Unpivot Columns.
2. Create calculated table.
Table = CALENDAR( DATE(2023,1,1), DATE( 2024,12,31))3. Create calculated column.
Year = YEAR('Table'[Date])Month = FORMAT('Table'[Date],"mmmm")month_Year = [Month]&" "&[Year]True/Flase = 'Table'[Date]>=DATE(2023,7,1)&&'Table'[Date]< DATE(2024,7,1)Flag = IF( 'Table_ALL'[Attribute] in SELECTCOLUMNS(FILTER(ALL('Table'),'Table'[True/Flase]=TRUE()),"1",'Table'[month_Year]),1,0)4. Create calculated table.
Flag_Table = var _table= FILTER( 'Table_ALL',[Flag]=1) var _table1= SUMMARIZE(_table, [Role Group],[Attribute],"Value",SUMX(FILTER(ALL('Table_ALL'),'Table_ALL'[Attribute]=EARLIER('Table_ALL'[Attribute])),[Value])) var _max=MAXX(_table1,[Value]) var _min=MINX(_table1,[Value]) var _table2= ADDCOLUMNS( _table1,"Flag", IF( [Value] = _min || [Value]=_max,1,0)) var _table3= FILTER(_table2,[Flag]=1) return _table35. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
1 Reply
Hi NMSJGHC ,
Here are the steps you can follow：
1. Select the month year column– Transform – Unpivot Columns.
2. Create calculated table.
Table = CALENDAR( DATE(2023,1,1), DATE( 2024,12,31))3. Create calculated column.
Year = YEAR('Table'[Date])Month = FORMAT('Table'[Date],"mmmm")month_Year = [Month]&" "&[Year]True/Flase = 'Table'[Date]>=DATE(2023,7,1)&&'Table'[Date]< DATE(2024,7,1)Flag = IF( 'Table_ALL'[Attribute] in SELECTCOLUMNS(FILTER(ALL('Table'),'Table'[True/Flase]=TRUE()),"1",'Table'[month_Year]),1,0)4. Create calculated table.
Flag_Table = var _table= FILTER( 'Table_ALL',[Flag]=1) var _table1= SUMMARIZE(_table, [Role Group],[Attribute],"Value",SUMX(FILTER(ALL('Table_ALL'),'Table_ALL'[Attribute]=EARLIER('Table_ALL'[Attribute])),[Value])) var _max=MAXX(_table1,[Value]) var _min=MINX(_table1,[Value]) var _table2= ADDCOLUMNS( _table1,"Flag", IF( [Value] = _min || [Value]=_max,1,0)) var _table3= FILTER(_table2,[Flag]=1) return _table35. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly