Forum Discussion

revansh's avatar
revansh
Icon for Helper IV rankHelper 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

revansh's avatar
revansh
Icon for Helper IV rankHelper 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
    Icon for Helper IV rankHelper 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?

    • v-ljerr-msft's avatar
      v-ljerr-msft
      Icon for Microsoft Employee rankMicrosoft Employee
      8 years ago

      Hi revansh,

      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.

      Not like measures, calculate columns/tables are computed during database processing(e.g. data refresh) and then stored in the model, they do not response to user selections on the report. So it is not possible to create a calculate column/table can change dynamically with user selections on the report. :smileyhappy:

       

      Regards

      • revansh's avatar
        revansh
        Icon for Helper IV rankHelper IV
        8 years ago

        Hi v-ljerr-msft Anonymous,

         

        Thanks for the reply.

        I am looking for a work around for my requirement. here is what i am trying to do... could you please provide your inputs.

         

        DIM TABLE             fact table(aCTUALS)               FACT TABLE2(tARGETS)

         

           KY                        KY     2                                                  KY      3

           IN                         IN      3                                                 IN       4

           CA                        CA     5                                                  CA      4

           TX                         TX      3                                                 TX        5

                                                                                                    ALL       10

         

        DIM TABLE AND 2 facts tables are joined on STATE column.

         

        i created a slicer on dim table state column. when no STATE is selected in the slicer, i want my KPI to show

         

        ALL    13(actual(SUm))    10(target)  ..... when a state is selected for example KY then my report should show

        KY       2                 3

         

        Hope it is clear

        Thanks