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 C...
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

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.

    • revansh's avatar
      revansh
      Helper IV
      8 years ago

      Let me put it this way:

       

      I have 2 tables: Table A and Table B (Structure is same but data is different).

      When a user selects an item in FILTER/SLICER selection, my calculated table should point to TABLE A if not then TABLEb.

       

      Hope it is clear now.

       

      Thanks

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        Do you have a relationship between these tables?