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
AndrejZitnay
Post Patron
Post Patron

Actual vs Budget problem with Invoices

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,

 

AndrejZitnay_2-1644745590006.png

 


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. 

AndrejZitnay_3-1644745711157.png

 


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.

 

AndrejZitnay_4-1644745859382.png

 

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)

AndrejZitnay_5-1644746100247.png


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

1 ACCEPTED SOLUTION
HotChilli
Super User
Super User

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.

 

View solution in original post

3 REPLIES 3
HotChilli
Super User
Super User

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
Post Patron
Post Patron

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
Super User
Super User

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.

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors