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
jd8766
Helper II
Helper II

Calculate % paid vs expected?

Hello, wondering if anyone has any ideas how to tackle this one...

I have the below 2 tables in my report (they are both joined in an SSAS model using contractID).

jd8766_0-1648413961429.png
The schedule table tells us for every contract id, how much money we expect to have received at any point in time. So we can see that on the 28th of Jan 2016, we expected to have £233.61 and then on the 1 feb 2019 we expected another £58.4. This needs to be a running total also.. so on the 1 feb 2019 the full total of 292.01 is expected.

The amount paid table below it are all the payments we have had for the contract.. I want to calculate at any point in time what the % payments vs expected was.

So If I was to look at the 1st of feb 2016, I would see a % of 57% as we expected to have 233.61 at that point, and we only had 134.26 (the £200 minus the -65.74)

Ideally I would just be able to see this monthly, so per month year the % of paid vs total expected up to that point.

Does anyone know the best way of achieving this? I am thinking I might need to create an unrelated date table to anchor the measures I will need. But really don't know where I would start with it?

Appreciate any advise on this one..

 

1 ACCEPTED SOLUTION
V-lianl-msft
Community Support
Community Support

Hi @jd8766 ,

 

As far as I know, cannot create a disconnected table in Power BI with Live connection .

Please try to use ALL and ALLEXCEPT function:

Measure = 
var total_ = CALCULATE(SUM(Table2[Amount]),FILTER(ALLEXCEPT(Table2,Table2[ContractID ]),Table2[Payment Date]<=MAX(Table2[Payment Date])))
var schedule_ = CALCULATE(SUM(Table1[Amount]),FILTER(ALL(Table1),Table1[Schedule]<=MAX(Table2[Payment Date])&&Table1[ContractID]=MAX(Table2[ContractID ])))
return divide(total_,schedule_)

 

Vlianlmsft_0-1648608635736.png

 


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

View solution in original post

3 REPLIES 3
V-lianl-msft
Community Support
Community Support

Hi @jd8766 ,

 

As far as I know, cannot create a disconnected table in Power BI with Live connection .

Please try to use ALL and ALLEXCEPT function:

Measure = 
var total_ = CALCULATE(SUM(Table2[Amount]),FILTER(ALLEXCEPT(Table2,Table2[ContractID ]),Table2[Payment Date]<=MAX(Table2[Payment Date])))
var schedule_ = CALCULATE(SUM(Table1[Amount]),FILTER(ALL(Table1),Table1[Schedule]<=MAX(Table2[Payment Date])&&Table1[ContractID]=MAX(Table2[ContractID ])))
return divide(total_,schedule_)

 

Vlianlmsft_0-1648608635736.png

 


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

Ashish_Mathur
Super User
Super User

Hi,

Share the link from where i can download your PBI file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi, it's a live SSAS connection so not sure you'd be able to open it?

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.