Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
karun_r
Employee
Employee

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 the current quarter.

 

Now, I need to have a dynamic measure which will have the total sales information for each quarter. But the caveat is, for the current quarter, this dynamic measure must represent the forecast value from the second table. I am using this dynamic measure to calculate how much of the sales% were achieved each day up until the last day of the quarter.  I have something like this as of now:

 

Table1 - Sales

Region

Fiscal Quarter

Date

SalesOnThisDay

CummulativeRevenue - Running total of the SalesOnThisDay  - Measure I am calculating

TotalQtrRevenue - Measure I am calculating to show the total sales in the quarter

DailySalesPercentage - CummulativeRevenue/TotalQtrRevenue - Measure I am calculating

 

 

Now for the current quarter, the problem is, if we consider somewhere in the middle of the quarter, the final value i.e. TotalQtrRevenue will be equal to the CummulativeRevenue and this does not make sense as it is not possible to reach 100% target by the middle of the quarter. For this reason, we are planning to use the forecast values from another table. It's strucutre is as follows

 

Region

Fiscal Quarter

Forecast

 

Now, how do I create my dynamic measure so that it will consider the forecast value for the current year instead of the actual sales? I tried with IF statements inside the measure formula and it is not working.

1 ACCEPTED SOLUTION

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.

View solution in original post

6 REPLIES 6
Ashish_Mathur
Super User
Super User

Hi,

 

Share the link from where i can download your file.  Please also show the expected result there.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
v-ljerr-msft
Employee
Employee

Hi @karun_r,

 

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

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

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)

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 uploadingSmiley Happy

 

Regards

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.

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

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.