Forum Discussion

NB3's avatar
NB3
Helper III
6 years ago
Solved

Use DAX formula in specific cell

Hi everyone,

 

Here is my problem, I'd like to know if it's possible to use a formula in a specific cell that would be the result of let's say cell1/cell2 or cell1*cell2 and so on (like you would do in Excel).

 

Right now we are using an Excel spreadsheet and the goal is to get rid of it so we replace just 2-3 values in PBI and the whole spreadsheet updates based on that (and the charts based on this data).

 

I hope that makes sense,

 

Thanks everyone,

 

Nick,

Need Help
  • Icey's avatar
    Icey
    6 years ago

    Hi NB3 ,

    Try this:

    1. Add an index column in Power Query Editor.

    2. UnPivot other columns.

    3. Create a measure.

    Divide =
IF (
    HASONEFILTER ( 'Table'[Index] ),
    MAX ( 'Table'[Value] ),
    DIVIDE (
        CALCULATE (
            MAX ( 'Table'[Value] ),
            FILTER ( ALLEXCEPT ( 'Table', 'Table'[Attribute] ), 'Table'[Index] = 1 )
        )
            + CALCULATE (
                MAX ( 'Table'[Value] ),
                FILTER ( ALLEXCEPT ( 'Table', 'Table'[Attribute] ), 'Table'[Index] = 3 )
            ),
        CALCULATE (
            MAX ( 'Table'[Value] ),
            FILTER ( ALLEXCEPT ( 'Table', 'Table'[Attribute] ), 'Table'[Index] = 2 )
        )
    )
)

    Or:

    Divide 2 =
VAR Value_Index_1 =
    CALCULATE (
        MAX ( 'Table'[Value] ),
        FILTER ( ALLEXCEPT ( 'Table', 'Table'[Attribute] ), 'Table'[Index] = 1 )
    )
VAR Value_Index_2 =
    CALCULATE (
        MAX ( 'Table'[Value] ),
        FILTER ( ALLEXCEPT ( 'Table', 'Table'[Attribute] ), 'Table'[Index] = 2 )
    )
VAR Value_Index_3 =
    CALCULATE (
        MAX ( 'Table'[Value] ),
        FILTER ( ALLEXCEPT ( 'Table', 'Table'[Attribute] ), 'Table'[Index] = 3 )
    )
RETURN
    IF (
        HASONEFILTER ( 'Table'[Index] ),
        MAX ( 'Table'[Value] ),
        DIVIDE ( Value_Index_1 + Value_Index_3, Value_Index_2 )
    )

    4. Create a matrix visual and change subtotal name.

    PBIX file attached.

     

    Best Regards,
    Icey

     

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

3 Replies

  • Icey's avatar
    Icey
    Community Support
    6 years ago

    Hi NB3 ,

    DAX is used for operations between columns. But there are some workarounds to perform operations between specific cells.

    Please show me your scenario, I'll test for you.

     

    Best Regards,
    Icey

     

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

     

    • NB3's avatar
      NB3
      Helper III
      6 years ago

      Hi Icey ,

       

      I'm not allowed to show you the data but here is a sample with formulas just to give you an idea (formula is in cell A10),

       

       

      Thanks for your help,

       

      Nick

      • Icey's avatar
        Icey
        Community Support
        6 years ago

        Hi NB3 ,

        Try this:

        1. Add an index column in Power Query Editor.

        2. UnPivot other columns.

        3. Create a measure.

        Divide =
IF (
    HASONEFILTER ( 'Table'[Index] ),
    MAX ( 'Table'[Value] ),
    DIVIDE (
        CALCULATE (
            MAX ( 'Table'[Value] ),
            FILTER ( ALLEXCEPT ( 'Table', 'Table'[Attribute] ), 'Table'[Index] = 1 )
        )
            + CALCULATE (
                MAX ( 'Table'[Value] ),
                FILTER ( ALLEXCEPT ( 'Table', 'Table'[Attribute] ), 'Table'[Index] = 3 )
            ),
        CALCULATE (
            MAX ( 'Table'[Value] ),
            FILTER ( ALLEXCEPT ( 'Table', 'Table'[Attribute] ), 'Table'[Index] = 2 )
        )
    )
)

        Or:

        Divide 2 =
VAR Value_Index_1 =
    CALCULATE (
        MAX ( 'Table'[Value] ),
        FILTER ( ALLEXCEPT ( 'Table', 'Table'[Attribute] ), 'Table'[Index] = 1 )
    )
VAR Value_Index_2 =
    CALCULATE (
        MAX ( 'Table'[Value] ),
        FILTER ( ALLEXCEPT ( 'Table', 'Table'[Attribute] ), 'Table'[Index] = 2 )
    )
VAR Value_Index_3 =
    CALCULATE (
        MAX ( 'Table'[Value] ),
        FILTER ( ALLEXCEPT ( 'Table', 'Table'[Attribute] ), 'Table'[Index] = 3 )
    )
RETURN
    IF (
        HASONEFILTER ( 'Table'[Index] ),
        MAX ( 'Table'[Value] ),
        DIVIDE ( Value_Index_1 + Value_Index_3, Value_Index_2 )
    )

        4. Create a matrix visual and change subtotal name.

        PBIX file attached.

         

        Best Regards,
        Icey

         

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