Actual vs Budget problem with Invoices

[AndrejZitnay]

Hello all,

I have issue with my visual.

PBIX can be downloaded from this link. Slow Free dowload should take 12 seconds.

https://ulozto.net/file/IvbI0fYg7rAG/actual-vs-budget-problem-with-invoices-pbix#_ga=2.142687989.119...

I have 4 tables,

Acutals, Budgets, Contract map & Nominal Code Map,

4 tables are linked via two tables and  I have realtionships between them.
All is working fine. I have as well date table but that has same concept. 

NC Table =

FILTER (

DISTINCT (

UNION ( VALUES(Actual[Nominal]), VALUES(Budget[Nominal]),VALUES('Nominal Code Map'[Nominal]))

),

ISBLANK([Nominal])=FALSE()

)

Contract Table =

FILTER (

DISTINCT (

UNION ( VALUES(Actual[Contract]), VALUES(Budget[Contract]),VALUES('Contract Map'[Contract]))

),

ISBLANK([Contract])=FALSE()

)

Up to now I was always showin Actual vs Budget & Variance on Contract & Nominal Level.
No issue with that. 

Now I need to show Inovices under nominals. I have separete visual which use Power apps and flow to donwload copy of invocie via API. That element is working fine but slecting right invoice is trigger for the flow. 

Problem is with Budget & Variance on Invoices.  Budgets are only on nominal Level and you can see that visual doens't work.
I have example with table and Hierarchical Filter from Xviz. I am using this filter as it is easier to filer one invoice for my additonal flow.

Are you able to help me out? I need to deliver this tomorrow and I am lost. 

I need to deliver one of these 3 outcomes.
Prefferably Option A
(Budget & Variance will not be shown on Invocies)

Option B have budget on all invoices. Not ideal but better than now.
Option C have Budget divided by number of invoices. 

Is there anyone whome can help me out?

Many thanks in advance.

Andrej

[HotChilli]

I can't help you with that, sorry.  I know nothing about the xviz hierarchy filter and half the visual is blocked for me (due to having to register it or something).

[AndrejZitnay]

Hello @HotChilli ,

You are star.

It does work on Matrix table.
It doesn't on hierarchy slicer.

Any idea why? We plan to use that Hierarchy slicer. Table Matrix was there only for better visual.

Please see updated versios with new measures.

https://ulozto.cz/tamhle/07fvHz2Q0Kx1/name/Nahrano-13-2-2022-v-19-09-06#!ZJRmZGR1LwtmMzL1AQExZGIxATH...

Many thanks for your help.

Andrej

[HotChilli · Accepted solution]

Budget and Variance values in the pictures provided don't match the data in the pbix. 

However, the problem is that you don't want to show values at the Invoice level so what about changing the measures to be like:

Budgets = IF (ISINSCOPE(Actual[Invoice]), BLANK(), sum(Budget[Budget]))

,

the Variance measure will also need changed.

Let me know what you think.