Forum Discussion

tsoares05's avatar
tsoares05
Frequent Visitor
3 years ago
Solved

Create a calculated column based on conditions

Hi,

I'm trying to create a calculated column based on the example below.

Basically, the rows that have a 'blank' version are the total income from each product. What I'm trying to achieve is the calculation on "Income % versions", displaying the percentage of income from each product version.

In this example I've calculated the value manually on Excel, just to show what I'm searching for.

 

I've tried a few formulas, but I don't know how to create this. Is this achievable?

Any help is more than welcome.

Thanks

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi tsoares05 ,

    Please try to create a new column with below dax formula:

    Income % versions =
VAR cur_pt = [Product]
VAR _a =
    CALCULATE (
        MAX ( [Income] ),
        FILTER ( 'Table' , [Product] = cur_pt && [Version] = BLANK () )
    )
VAR _b = [Income]
VAR cur_ver = [Version]
VAR _val =
    FORMAT ( DIVIDE ( _b, _a ), "Percent" )
RETURN
    IF ( cur_ver = BLANK (), BLANK (), _val )

    Please refer the attached .pbix file.

     

    Best regards,
    Community Support Team_Binbin Yu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi tsoares05 ,

    Please try to create a new column with below dax formula:

    Income % versions =
VAR cur_pt = [Product]
VAR _a =
    CALCULATE (
        MAX ( [Income] ),
        FILTER ( 'Table' , [Product] = cur_pt && [Version] = BLANK () )
    )
VAR _b = [Income]
VAR cur_ver = [Version]
VAR _val =
    FORMAT ( DIVIDE ( _b, _a ), "Percent" )
RETURN
    IF ( cur_ver = BLANK (), BLANK (), _val )

    Please refer the attached .pbix file.

     

    Best regards,
    Community Support Team_Binbin Yu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    3 years ago

    Hi,

    This should be solved with a measure - not with a calculated column.  If my approach suits you, then share data in a fomat that can be pasted in an MS Excel file.