Forum Discussion

Lwpro's avatar
Lwpro
Frequent Visitor
2 years ago
Solved

SUMX STRING

Hello everyone, how are you?

I would like to ask for your help because I am facing a problem. I need to SUM the values from a column that is of type String.

The values I need to sum are those referring to prices.
For example:
$1.99
$5.99
...

However, in my data, they come with periods and various other results:
1.99
5.99
02-6541-23135-sagafa-54684
true
false
...

In Power BI, I can view the values by filtering through another table, which is the [RETURN] field, and filtering by Price.

I would like to ask for help on how I can proceed to do the Sum. I don't know if there is any DAX formula that can help me or if I need to make changes in Power Query.

Thank you in advance for your help.

Thank you.

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Lwpro 

     

    Please try this:

    First of all, a table created:

    Then add a measure:

    MEASURE =
VAR _number =
    ADDCOLUMNS (
        ALLSELECTED ( 'Table' ),
        "Num",
            IF (
                MID ( 'Table'[value], 1, 1 ) = "$",
                VALUE ( MID ( 'Table'[value], 2, 5 ) )
            )
    )
RETURN
    SUMX ( _number, [Num] )

    The reslut is as follow:

    If the above one can't help you, could you please provide more raw data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples? It would be helpful to find out the solution. You can refer the following links to share the required info:

    How to provide sample data in the Power BI Forum

    How to Get Your Question Answered Quickly

    And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

    How to upload PBI in Community

    Best Regards

    Zhengdong Xu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Lwpro 

     

    Please try this:

    First of all, a table created:

    Then add a measure:

    MEASURE =
VAR _number =
    ADDCOLUMNS (
        ALLSELECTED ( 'Table' ),
        "Num",
            IF (
                MID ( 'Table'[value], 1, 1 ) = "$",
                VALUE ( MID ( 'Table'[value], 2, 5 ) )
            )
    )
RETURN
    SUMX ( _number, [Num] )

    The reslut is as follow:

    If the above one can't help you, could you please provide more raw data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples? It would be helpful to find out the solution. You can refer the following links to share the required info:

    How to provide sample data in the Power BI Forum

    How to Get Your Question Answered Quickly

    And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

    How to upload PBI in Community

    Best Regards

    Zhengdong Xu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.