Sales/Manual targets, depending on a grouped value

[PolinaOr]

Hi, everyone

I am only starting writing DAX formulas and learning, and recently I have been asked to build an dashboard from current Excel one, and it proves to be difficult. I would need support with building DAX formula from following values:

1) Actual sales - from measures table

2) Subsidiary - from values table

These 2 are linked inside the data model, but are not in 1 table.

3) Manually input target for Target FY25 per each Sub

I have a limitation, where I cannot use DATATABLE, because the datasource won't allow addition of new tables, so I will have to resort to manually inputting values for each sub for now, though there are not that many - 18 lines.

I basically need to replicate table I attached, but in PBI (I will then see how to make it more "accurate"and presentable, for now the issue is this specific formula), where the Target column will need to be manually entered in a Measure.

The Target will be needed to be divided by the current FY month - this data is also available in the "Time" values table and is assigned to each date-time value in there. For example, April is 9th FY month, so you will see 9th month in front of all April values.

 Thank you very much in advance

[danextian]

Hi @PolinaOr 

What do you mean by "I have a limitation, where I cannot use DATATABLE, because the datasource won't allow addition of new tables"? If you have access to the model, you should be able to do it. It only isn't possible to add new tables if you are creating thin reports and any modification to the tables and column must be done to the semantic model. 

Also, can you please provide a workable sample data (not an image), your expected result from the same sample data and your reasoning behind? You may post a link to Excel or a sanitized copy of your PBIX stored in the cloud.

[PolinaOr]

Current semantic model imposes this limitation. We are currently in the transition process, new one will allow we to use DATATABLE. Once its available i will switch to it to create matching table.

Sample data here: sample data.xlsx

[donbuser · Accepted solution]

Hi! This is a really good question — and honestly a pretty common scenario when you’re transitioning from Excel to Power BI.

Given your constraints (no new tables like DATATABLE), you can still handle this using a SWITCH-based measure to manually map targets to each Subsidiary.

Step 1: Create a Target Measure (Manual Mapping)

You can define your targets like this:

Target FY25 =
SWITCH(
SELECTEDVALUE('Subsidiary'[Subsidiary]),
"Sub A", 100000,
"Sub B", 150000,
"Sub C", 120000,
...
BLANK()
)

This lets you manually assign a target per subsidiary without needing a separate table.

Step 2: Get Current Fiscal Month

Assuming your Time table has a fiscal month column:

Current FY Month = MAX('Time'[FiscalMonthNumber])
Step 3: Divide Target by Fiscal Month

Now you can create your adjusted target:

Target per Month =
DIVIDE(
[Target FY25],
[Current FY Month]
)
A Couple Notes
SELECTEDVALUE works well here as long as your visual is filtered to a single Subsidiary (which it usually is in tables/matrices).
This approach is fine for a smaller number of Subsidiaries (like your 18), but longer-term, you’ll probably want to move this into a proper table when your data source allows it.
You’re essentially recreating what would normally be a lookup table, just inside a measure.
Big Picture

You’re on the right track — this kind of problem is less about complex DAX and more about working around model constraints.

Once you get past this step, you’ll find it much easier to refine the structure and make it more scalable.

[PolinaOr]

Thank you very much! With little tweaks this approach works - i will need to do a separate formula for the latest FY month, as MAX of the latest FY month would be 12, and not the current, so I will need to do it based on value of the FY month assigned to today's date