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
Here is the updated file as requested https://www.dropbox.com/t/GpKbc42ndoicPxR5
Value (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 )