Missing Values in SUMX Function

[Anonymous]

Hi,

For context, I have sales data and forecast data for each department in a company. The sales data is a feed from SQL and the forecast data is a table in PowerBI as shown below (where each department D1, D2 etc has it's own column).

I want to create a table/matrix in the dashboard where the user can select the department via a slicer (this bit is done) and then the sales and forecast numbers for that department are shown like below. The issue is that it seems where a department had no sales in a month (September 2021 to Feb 2022 in this case) it not only doesn't show the sales but it also doesn't show the forecast either for those months.

I am using the following SWITCH function in a Measure to collect the forecast data:

Forecast Revenue = SWITCH(
    TRUE(),
    SELECTEDVALUE(Revenue[Department Name])="D1",SUMX(Budgets,[D1 - Revenue]),
    SELECTEDVALUE(Revenue[Department Name])="D2",SUMX(Budgets,[D2 - Revenue]),
    SELECTEDVALUE(Revenue[Department Name])="D3",SUMX(Budgets,Budgets[D3 - Revenue]),
    SELECTEDVALUE(Revenue[Department Name])="D4",SUMX(Budgets,[D4 - Revenue]),
    SELECTEDVALUE(Revenue[Department Name])="D5",SUMX(Budgets,[D5 - Revenue]),
    SELECTEDVALUE(Revenue[Department Name])="D6",SUMX(Budgets,Budgets[D6 - Revenue]),
    SELECTEDVALUE(Revenue[Department Name])="D7",SUMX(Budgets,[D7 - Revenue]),
    SELECTEDVALUE(Revenue[Department Name])="D8",SUMX(Budgets,Budgets[D8 - Revenue])
    )

The relationship between Budget and Revenue is as follows. This is definitely having an effect as when I deactivate the relationship between Revenue and Dates I get some luck however it still depends on there being some sales in all months which isn't always guaranteed. 

Please could anyone assist (also let me know if you need further clarification)?

Many thanks

[v-zhangti]

Hi, @Anonymous 

Is it possible to provide sample data for Revenue[Department Name] and how this relates to the Budgets table? Provide as much data detail as possible, design to sensitive data please remove in advance.

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[johnt75]

Try changing the relationships with the Dates table to be one-to-many, with Dates on the one side, and make sure that they are single directional. You want the filters to flow from the Dates table to the others, you do not want the filters flowing in both directions.