Calculated Table

[revansh]

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

[KHorseman]

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]

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

[KHorseman]

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]

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

[v-ljerr-msft]

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. 

Regards

[revansh]

Hi @v-ljerr-msft @KHorseman,

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

[v-ljerr-msft · Accepted solution]

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. 

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. 

Regards

[revansh]

It worked.

Thank you very much.

[KHorseman]

Do you have a relationship between these tables?