Forum Discussion

karun_r's avatar
karun_r
Microsoft Employee
8 years ago
Solved

Dynamic Measures using different tables

I have two tables. One has the total sales information for each region including current quarter. And this data is day wise. The second table will have the forecasted sales values for each region for...
Need Help
  • karun_r's avatar
    karun_r
    8 years ago

    Thanks, but I fixed it myself by using some ISFILTERED, MAX and other DAX functions. Had to use some flags as well to get to the final result.

v-ljerr-msft's avatar
v-ljerr-msft
Microsoft Employee
8 years ago

Hi karun_r,

 

Could you try using the formula below to see if it works in your scenario? :smileyhappy:

TotalQtrRevenue =
VAR currentQtr =
    CALCULATE ( MAX ( Sales[Fiscal Quarter] ), ALL ( Sales ) )
VAR currentQtrForcastRevenue =
    CALCULATE (
        SUM ( Forecast[Forecast] ),
        FILTER (
            ALL ( Forecast ),
            Forecast[Region] = MAX ( Sales[Region] )
                && Forecast[Fiscal Quarter] = currentQtr
        )
    )
RETURN
    IF (
        MAX ( Sales[Fiscal Quarter] ) = currentQtr,
        currentQtrForcastRevenue,
        [TotalRevenue]
    )

 

Regards

  • karun_r's avatar
    karun_r
    Microsoft Employee
    8 years ago

    No this still returns the TotalRevenue for the current quarter instead of the Forecast value.

  • karun_r's avatar
    karun_r
    Microsoft Employee
    8 years ago

    I think the problem with your formula is that, it is treating the fiscal quarter and region as two seperate entities. But in my scenario, the relationship between the sales and the forecast tables is established using a psuedo composite key (concatenation of fiscal quarter and region because of the lack of the composite key option in PowerBI)

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

      Hi karun_r,



      I think the problem with your formula is that, it is treating the fiscal quarter and region as two seperate entities. But in my scenario, the relationship between the sales and the forecast tables is established using a psuedo composite key (concatenation of fiscal quarter and region because of the lack of the composite key option in PowerBI)

      Could you post your table structures(including the relationships) with some sample/mock data and the expected result, so that we can better assist on this issue? It's better to just share a dummy pbix file which can reproduce the issue. You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading. :smileyhappy:

       

      Regards

      • karun_r's avatar
        karun_r
        Microsoft Employee
        8 years ago

        Thanks, but I fixed it myself by using some ISFILTERED, MAX and other DAX functions. Had to use some flags as well to get to the final result.