https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/How-to-calculate-the-average-size-of-size-list-text-in-Direct/m-p/3495226#M133901 <P>hi&nbsp;Anonymous</LI-USER>&nbsp;</P> <P>do you have more sample data?</P> <P>the digit could be extracted with LEFT, RIGHT functions with DAX and PQ.&nbsp;</P> Wed, 25 Oct 2023 03:48:59 GMT FreemanZ 2023-10-25T03:48:59Z https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/How-to-calculate-the-average-size-of-size-list-text-in-Direct/m-p/3495219#M133899 <P>In short, Now I have a text column like&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;//maybe more than 3 size pairs</P><P>"24x24 36x36 64x80"</P><P>Now I have to calculate the average width (24 + 36 +64) / 3&nbsp; &nbsp;</P><P>&nbsp;</P><P>further, I need to calculate how many square in the above text. (2)</P><P>&nbsp;</P><P>Notice: I use<SPAN>&nbsp;</SPAN><STRONG>direct query</STRONG><SPAN>&nbsp;</SPAN>mode now.&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Use DAX or power query are both OK.</P> Wed, 25 Oct 2023 03:46:21 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/How-to-calculate-the-average-size-of-size-list-text-in-Direct/m-p/3495219#M133899 Anonymous 2023-10-25T03:46:21Z https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/How-to-calculate-the-average-size-of-size-list-text-in-Direct/m-p/3495226#M133901 <P>hi&nbsp;Anonymous</LI-USER>&nbsp;</P> <P>do you have more sample data?</P> <P>the digit could be extracted with LEFT, RIGHT functions with DAX and PQ.&nbsp;</P> Wed, 25 Oct 2023 03:48:59 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/How-to-calculate-the-average-size-of-size-list-text-in-Direct/m-p/3495226#M133901 FreemanZ 2023-10-25T03:48:59Z https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/How-to-calculate-the-average-size-of-size-list-text-in-Direct/m-p/3495237#M133902 <P>we can make it manually,</P><P>[size_list]</P><P>"12x23 20x20 30x30 512x512"</P><P>"1024x768 1920x1080"</P><P>"2080x3070 1x1 2x2 3x4 4x5 5x6 6x7 7x8"</P><P>like these</P> Wed, 25 Oct 2023 03:56:13 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/How-to-calculate-the-average-size-of-size-list-text-in-Direct/m-p/3495237#M133902 Anonymous 2023-10-25T03:56:13Z https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/How-to-calculate-the-average-size-of-size-list-text-in-Direct/m-p/3500567#M134211 <P>hi&nbsp;Anonymous</LI-USER>&nbsp;,</P> <P>&nbsp;</P> <P>you can try with Power Query. check what is attached.&nbsp;</P> <P><img /></P> <P>&nbsp;</P> Fri, 27 Oct 2023 06:13:43 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/How-to-calculate-the-average-size-of-size-list-text-in-Direct/m-p/3500567#M134211 FreemanZ 2023-10-27T06:13:43Z https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/How-to-calculate-the-average-size-of-size-list-text-in-Direct/m-p/3500838#M134229 <P>The&nbsp;Table.SplitColumn is not supported in direct query mode.</P><P><img /></P><P>&nbsp;</P> Fri, 27 Oct 2023 08:21:53 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/How-to-calculate-the-average-size-of-size-list-text-in-Direct/m-p/3500838#M134229 Anonymous 2023-10-27T08:21:53Z https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/How-to-calculate-the-average-size-of-size-list-text-in-Direct/m-p/3506189#M134500 <P>Hi&nbsp;Anonymous</LI-USER>&nbsp;,</P> <P>You can create a calculated column as below to get it, please find the details in the attachment.</P> <LI-CODE lang="markup">Average width = VAR _replace1 = SUBSTITUTE ( 'Table'[size_list], " ", "|" ) VAR SplitBySpace = PATHLENGTH ( _replace1 ) VAR _replace2 = SUBSTITUTE ( _replace1, "x", "|" ) VAR SplitBySpace2 = PATHLENGTH ( _replace2 ) VAR Mylen = LEN ( _replace2 ) VAR mytable = ADDCOLUMNS ( GENERATESERIES ( 1, SplitBySpace2 ), "mylist", VALUE ( PATHITEM ( _replace2, [Value] ) ) ) VAR Result = DIVIDE ( SUMX ( FILTER ( mytable, MOD ( [Value], 2 ) = 1 ), [mylist] ), SplitBySpace, 0 ) RETURN Result</LI-CODE> <P><img /></P> <P>Best Regards</P> Tue, 31 Oct 2023 03:27:10 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/How-to-calculate-the-average-size-of-size-list-text-in-Direct/m-p/3506189#M134500 Anonymous 2023-10-31T03:27:10Z