Filtering

[lucasocunha]

So, I have two tables in my BI, one to manage hired projects and one to manage their payments. The first has itens like the name of it and the hired value. The second has details of the payments, which aren't always made in one installment. Both have one columm called "Budget year", displayed like "17/18", 15/16", etc, to indetify the year in which it was hired and payed. They aren't allways the same: One project can be hired in one year, and be payed in another.

I have to develop a table that shows the project name, the hired value, the executed value and the value yet to be paid for. And to add a slicer for "budget year". The table needs to show only projects that were payed for in the year selected, not depending on which year it was hired.

I've created then 3 measures: sum of hired values, sum of executed values and to be paid values (first - second). 

The problem is: When I filter the values for one year, this filter doesn't affect the sum of hired values, because it is in another table and they are connected only by their names. Because of it, the table displays every project, and filters only the executed columm. In addition, the to be paid columm shows wrong values. As the filter eliminates payments that weren't executed in the year, the to be paid value equals the hired for projects that weren't payed in it. 

How do I solve this?

[MFelix · Accepted solution]

Hi @ lucasocunha,

You meed to creata a dimension table with the year for you to use as a slicer the create a measure that uses that alicer value to return the amounts you need.

Having the slicer based on a column from your related data will give you filtering only on one side of the data.

Can you share some sample data and ecpected result.

Not currently on computer to setup an example but I can retur to this later.

Regards,
MFelix

[MFelix · Accepted solution]

Hi @ lucasocunha,

You meed to creata a dimension table with the year for you to use as a slicer the create a measure that uses that alicer value to return the amounts you need.

Having the slicer based on a column from your related data will give you filtering only on one side of the data.

Can you share some sample data and ecpected result.

Not currently on computer to setup an example but I can retur to this later.

Regards,
MFelix

[lucasocunha]

Do you mean, entering the budget year values manually, connecting it to both the "budget year" columms and then use it as a slicer? I've done it, but the problem is that it filters also projects hired in the selected year. Because of that, I can't display in the table projects that were payed for in the year, but were hired in another. 

I don't know if this is what you ment. Also, unfortunately I can't share the data because it's confidential.

Thank you a lot,

I'll wait for your answer

[MFelix]

Hi @lucasocunha,

I meant to have a table however that table should not be related with the other two tables you need to make your calculations based on the slicer.

Can you make some mockup data just for trying to make the setup of the measures and expected results based on tha mock up, or at least the strucuture of the tables and how their are related.

If need share the file trough private message so that isn't public.

Regards,

MFelix

[lucasocunha]

Ok. Here is some mock up values:

name   hired value    budget year

1	34	15/16
2	120	15/16
3	600	16/17
4	2323	16/17
5	8000	17/18
6	2423	17/18

name  payment value   budget year of payment

1	34	15/16
2	40	15/16
2	40	15/16
2	40	16/17
3	200	16/17
3	200	16/17
3	200	16/17
4	2323	17/18
5	4000	17/18
5	4000	17/18
6	2423	18/19

[MFelix]

Hi @lucasocunha,

Create a dimension table for the projects name and a dimension table for the years.

Make a relationship between Projects and the other two table and an inactive relationship between years and payments.

Then add the following measures: 

hired = SUM(Budget[Hired Value])


executed value =
CALCULATE (
    SUM ( payment[Payment Value] );
    USERELATIONSHIP ( Years[year]; payment[Budget year of payment] )
)

to be paid = [hired] - [executed value]

Then place your values in a table like this:

Project Name - Table Project

hired

Executed value

to be paid

Add a filter based on executed value to be different of blanks this will givc you the expected result when you make the slicer based on the table year.

See attach PBIX file.

If you want to have cumulative values  you need to add the following two measures:

executed value cumulative = 
var selected_year = MAX(Years[idd])
return
CALCULATE (
    SUM ( payment[Payment Value] ); payment[YEARID] <= selected_year)

to be paid cumulative = [hired] - [executed value cumulative]

In the years table also create an ID column and in the payment table create a YEARSID column with the following syntax:

YEARID = LOOKUPVALUE(Years[ID];Years[year];payment[Budget year of payment])

this will allow to calculate the cumulative see result below, be aware that in this new table the filter of executed value not blank is still used this is to filter only projects payed oin that year if you only use the executed value cumulative you will get all the projects on the previous years.

Attach PBIX file with example.

Regards

MFelix

[lucasocunha]

Thank you a lot Mfelix.

But there is still one thing wrong: When i make a table displaying:

Name       hired    executed    to be paid cumulative

which is exactly what I want to show, there is one thing wrong when I use the slicer:

The to paid paid columm shows wrong values if one project wasn't payed until the year selected, but was executed later.

To understand it easier, what I need to show in this visualization is: What have we payed this year? For those projects that were payed for, how much was the hired value and what remains to be payed? (today and not in the moment determined by the slicer)

But you helped a lot,

Hope we can solve this one!

TY

[MFelix]

Hi @lucasocunha,

To what I can understand you want the cumulative values for a given project example:

Project 1 with payments on year 1, 2 , 3 when you choose year 2 it will give you payments of year 1 and 2 correct?

This is the second part of my post where I refer the 

"If you want to have cumulative values  you need to add the following two measures:

executed value cumulative = 
var selected_year = MAX(Years[idd])
return
CALCULATE (
    SUM ( payment[Payment Value] ); payment[YEARID] <= selected_year)

to be paid cumulative = [hired] - [executed value cumulative]

In the years table also create an ID column and in the payment table create a YEARSID column with the following syntax:

YEARID = LOOKUPVALUE(Years[ID];Years[year];payment[Budget year of payment])

this will allow to calculate the cumulative see result below, be aware that in this new table the filter of executed value not blank is still used this is to filter only projects payed oin that year if you only use the executed value cumulative you will get all the projects on the previous years.

"

Did you tried this or is this not the expect result?

Regards

MFelix

[lucasocunha]

Hi! Sorry for the long wait.

No, thats not exactly what I wanted. 

Project 1 with payments on  year 1 2 3. When I choose year 2, It should give me how much was paid in year 2 and how much is left to be paid couting with payments from 1 2 and 3.

If project 1 costs 120 USD, and I paid 60 on year 1 and 40 on year 2, if i choose year 1 I want the result to be:

Name = Project 1

executed current budget =60

to be paid = 20

If I choose year 2, i should get:

Name: Project 1

Executed current budget = 40

To be paid = 20

It's a little difference, but important. 

Ty

[MFelix · Accepted solution]

Hi @lucasocunha,

You just need to change the formula Executed value cumulative to ignore all the filters you have for the year.

So in summary you will have:

hired = SUM(Budget[Hired Value])

executed value = 
CALCULATE (
    SUM ( payment[Payment Value] );
    USERELATIONSHIP ( Years[year]; payment[Budget year of payment] )
)


executed value cumulative = 
var selected_year = MAX(Years[ID])
return
CALCULATE (
    SUM ( payment[Payment Value] ); ALL(Years[ID]))

to be paid cumulative = [hired] - [executed value cumulative]

Hired - Total Hired

Executed value - Presents value paid in the selected year

Executed value cumulative - will calculate total payed amounts on all the years no matter the selection

To be Paid cumulative  - difference between Total Hired and Cumulative value so since cumulative is always summing total payed no matter the year it will be full in debt.

See resul below I have all the columns for you to see the differences between the calculations as you can see if I select 2 years in both rows the value to be paid is the same although the payment in both years is different.

See attach PBIX only project 2 as the payments in several years but the measures will work for all projects.

Regards,

MFelix

[lucasocunha]

Thank you very much!