Forum Discussion

Oded-Dror's avatar
Oded-Dror
Helper III
4 years ago
Solved

Dynamic value in calculated column

Hi there,

Please take a look at this picture (AdventureWorksDW)
And tell me if this is possible to store value in calculated column based on dynamic value from a measure.

I've try (see my code) but I got wrong results. - Every time I click on Country I would like to see the revenue change in the calculated column.

Thanks,

Oded Dror

Need Help
  • jeroendekk's avatar
    jeroendekk
    4 years ago

    Hi Oded-Dror 
    Calculated columns and tables are calculated on refresh and after that they are static.

     

    So a slicers will impact which rows of a table are used but not the content itself. A measure could create this result, but you cannot use measure results in a slicer.

    So sorry I do not think this is possible.

    Best regards,

    Jeroen Dekker

6 Replies

  • jeroendekk's avatar
    jeroendekk
    Responsive Resident
    4 years ago

    Hi Oded-Dror 
    Calculated columns and tables are calculated on refresh and after that they are static.

     

    So a slicers will impact which rows of a table are used but not the content itself. A measure could create this result, but you cannot use measure results in a slicer.

    So sorry I do not think this is possible.

    Best regards,

    Jeroen Dekker

  • Kumail's avatar
    Kumail
    Impactful Individual
    4 years ago

    Hello Oded-Dror 

     

    If you could send a sample .pbix that demonstrates what you are looking to get. It would really help to provide you with a quick solution.

     

    Regards
    Kumail Raza

  • Oded-Dror's avatar
    Oded-Dror
    Helper III
    4 years ago

    Is AdventureWorksDW database wich I added Calculated column to the product table

    Sub Category Value = 'Product'[Subcategory] & " (" &
    Format( CALCULATE([Revenue],
    Filter('Product','Product'[ProductKey] = EARLIER('Product'[ProductKey])),
    KEEPFILTERS(Customer)
    ), "#,##") & ")"
     
    When I put it in a slicer it show total per subcategory not respecting the Customer Country-Region filter
    Revenue is SUM(Sales[Sales Amount]) (Link below the image)
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Community Support
    4 years ago

    Hi Oded-Dror ,

    According to your description, you want the calculated column slicer changed according to the country slicer, it cannot be achieved, as tables and calculated columns are established prior to the query and don't change - only get filtered. So the calculated column can be calculated the right value in the data view, then it will be filtered by the country slicer.

    In your snapshot, the sub category value depends on the measure revenue, I guess the measure has a SUM function, so the value will always be the sum value. You can modify your measure, or post the formula of the measure if needed, as your sample link cannot be connected.

    Best Regards,
    Community Support Team _ kalyj

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