Forum Discussion
Create a visualization in tabular form
- 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 )
Any solutions for above problem? Kindly help me anyone on this
Thanks in advance
SK87 are these values 9' 3'' text in your data?
I understand this is 9 feet and 3 inches but is it some formatting you did on numerical values or it's just text?
In general if these are numbers you could just need to unpivot the A and B columns and create a MAX and MIN measure on the new value column.
- SK874 years agoHelper III
SpartaBI This is text value
tamerj1 While converting the values to Inches I am getting below error as Column B value is Text
- tamerj14 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.- SK874 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"