cancel
Showing results 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

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).

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
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_)``````

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

3 REPLIES 3
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_)``````

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

Super User

Hi,

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper II

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

Announcements

#### 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 Monthly Update - June 2024

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

#### 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
Top Kudoed Authors