Forum Discussion
SK87
4 years agoHelper III
Create a visualization in tabular form
Hi All, Here is one problem which I am facing while creating a tabular graph - I have values in different columns and need to show minimum & Maximum values in one table of respective columns and ...
- 4 years ago
SK87
Here is the updated file as requested https://www.dropbox.com/t/GpKbc42ndoicPxR5Value (Inch) = VAR String = Data[Value] VAR Items = SUBSTITUTE ( String, " ", "|" ) VAR Length = PATHLENGTH ( Items ) VAR T1 = GENERATESERIES ( 1, Length, 1 ) VAR T2 = ADDCOLUMNS ( T1, "@Value", VAR SingleItem = PATHITEM ( Items, [Value] ) VAR ItemLength = IF ( CONTAINSSTRING ( SingleItem, "'" ) || CONTAINSSTRING ( SingleItem, """" ), LEN ( SingleItem ) - 1, LEN ( SingleItem ) ) VAR Number = LEFT ( SingleItem, ItemLength ) RETURN IF ( CONTAINSSTRING ( SingleItem, "'" ), 12 * IFERROR ( VALUE ( Number ), BLANK () ), IFERROR ( VALUE ( Number ), BLANK () ) ) ) RETURN SUMX ( T2, [@Value] )Min = VAR CurrentAttribute = SELECTEDVALUE ( Data[Attribute] ) VAR MinValue = MIN ( Data[Value (Inch)] ) VAR Foot = QUOTIENT ( MinValue, 12 ) VAR Inch = MOD ( MinValue, 12 ) RETURN IF ( CurrentAttribute = "B", IF ( Inch > 0, Foot & "' " & Inch & """", Foot & "'" ), MinValue )Max = VAR CurrentAttribute = SELECTEDVALUE ( Data[Attribute] ) VAR MaxValue = MAX ( Data[Value (Inch)] ) VAR Foot = QUOTIENT ( MaxValue, 12 ) VAR Inch = MOD ( MaxValue, 12 ) RETURN IF ( CurrentAttribute = "B", IF ( Inch > 0, Foot & "' " & Inch & """", Foot & "'" ), MaxValue )Mode = VAR T1 = ADDCOLUMNS ( Data, "@Frequency", CALCULATE ( COUNT (Data[Value] ), ALLEXCEPT ( Data, Data[Value (Inch)] ) ) ) VAR T2 = TOPN ( 1, T1, [@Frequency] ) VAR T3 = DISTINCT ( SELECTCOLUMNS ( T2, "@Value", [Value], "@ValueInch", [Value (Inch)] ) ) RETURN CONCATENATEX ( T3, [@Value], UNICHAR ( 10 ), [@ValueInch], ASC )
tamerj1
4 years agoCommunity Champion
SK87
Can you please share a screenshot of the source data? I just need to see how the values look like and what is the difference between your data and the sample data that I used in my sample file.
SK87
4 years agoHelper III
Here is the sample data and I am not able to open the data shared by you.
| A | B | C | D | E |
| 60 | 168 | 9' | ||
| 42 | 143.5 | |||
| 42 | 143.5 | |||
| 56 | 167.7 | 8' | ||
| 55 | 172 | 10000 | ||
| 16' | ||||
| 145 | 9' 3" | |||
| 145 | 9' 3" | |||
| 145 | 9' 3" | |||
| 204 | 270 | 26000 |
After unpivoting I am getting :
| Attribute | Blank | Value |
| A | 60 | |
| E | 168 | |
| B | 9' | |
| A | 42 | |
| E | 143.5 | |
| A | 42 | |
| E | 143.5 | |
| A | 56 | |
| E | 167.7 | |
| B | 8' | |
| A | 55 | |
| E | 172 | |
| D | 10000 | |
| B | 16' | |
| E | 145 | |
| B | 9' 3" | |
| E | 145 | |
| B | 9' 3" | |
| E | 145 | |
| B | 9' 3" |