Forum Discussion

SK87's avatar
SK87
Helper III
4 years ago
Solved

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 ...
  • tamerj1's avatar
    4 years ago

    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 )