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

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

v-ljerr-msft's avatar
v-ljerr-msft
Icon for Microsoft Employee rankMicrosoft Employee
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

    It worked.

     

    Thank you very much.