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
    tamerj1
    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 )

     

tamerj1's avatar
tamerj1
Community Champion
4 years ago

Hi SK87 
First you need to unpivot A and B columns using power query. Then a new column shall be created that converts all values to "Inch". The Min and Max measures shall be based on this column. Here is a sample file for your reference https://www.dropbox.com/t/RlIw4N0W7oLYqJRt

 

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 * VALUE ( Number ), VALUE ( Number ) )
    )
RETURN
    SUMX ( T2, [@Value] )
Min = 
VAR MinValue = MIN ( Data[Value (Inch)] )
VAR Foot = QUOTIENT ( MinValue, 12 )
VAR Inch = MOD ( MinValue, 12 )
RETURN 
    IF ( 
        Inch > 0,
        Foot & "' " & Inch & """",
        Foot & "'"
    )
Max = 
VAR MinValue = MAX ( Data[Value (Inch)] )
VAR Foot = QUOTIENT ( MinValue, 12 )
VAR Inch = MOD ( MinValue, 12 )
RETURN 
    IF ( 
        Inch > 0,
        Foot & "' " & Inch & """",
        Foot & "'"
    )