Forum Discussion

revansh's avatar
revansh
Helper IV
8 years ago
Solved

Calculated Table

Hi ,

 

nee dyour inputs on below calculated table scenario.

 

TAbleA:

 

Col1

col2

Col3

..

..

Col50

 

i want a calculated with 4 columns from above table.

 

Calculate table:

 

Col1

Col15

col20

col50

 

Thanks in advance

Need Help
  • v-ljerr-msft's avatar
    v-ljerr-msft
    8 years ago

    Hi revansh,

     

    If I understand you correctly, you should be able to use the formula below to create a new measure to calculate the Target based on selection of the Slicer in your scenario. :smileyhappy:

    Measure = 
IF (
    HASONEVALUE ( TARGETS[STATE] ),
    SUM ( TARGETS[TARGET] ),
    CALCULATE (
        SUM ( TARGETS[TARGET] ),
        FILTER ( ALL ( TARGETS ), TARGETS[STATE] = "ALL" )
    )
)

     

    Here is the sample pbix file for your reference. :smileyhappy:

     

    Regards

9 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Go to the Modeling tab and hit New Table. Use this formula:

     

    Table_ B = SUMMARIZE(
	Table_A,
	Table_A[Col1], Table_A[Col15], Table_A[Col20], Table_A[Col50]
)
    • revansh's avatar
      revansh
      Helper IV
      8 years ago

      Thanks KHorseman.

       

      One more quick help please.

       

      I have a target table as below. Target for each state and 1 row for all states. 

       

      STATE   TARGET_SALES

      KY           100

      IN            150

      TX            200

      CA           300

      ALL          1000

       

      I have a slicer/filter on STATE. when a user selects a STATE i want to consider that state TARGET and when nothing is selected in filter i want to consider "ALL"

       

      can we do it with calculated columns?

       

      Thanks

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        I'm not sure I understand the problem. What you're describing is the way slicers should work already. If nothing is selected in a slicer, all available items are used in the calculation.