Power BI matrix showing time different for different columns

[Tom02]

Hello,

I made a matrix in which a column is divided by the matrix between proceeds and costs. However, I want to show Proceeds in total (not didived by months or years) and Costs divided by year and for this year (2024) in particular also divided by month. In Excel you can simply do this by hiding, but I don't know how this works in Power BI. Using include or exclude is not giving what I want. When I use the drill function only the totals are shown, or all the month are shown, but the point is that Proceeds and Costs have to treated differently, although their values comes from the same column in the data.

Does anyone know the measure of function to do this?

Thanks in advance.

Regards,
Tom

[Tom02 · Accepted solution]

We solved the problem as following:

Use the merge function to get the data from (in this case) three tables into one.

This provided that I knew for every value or it is a proceed or a cost.

Then I made a conditional column, which stated: if it is a proceed give zero, if it is a cost, give me the year from another column.

This new column I used as the key to link it to the date table.

By doing this, Proceeds is only given as a total, whereas Total Costs is divided by months.

[Tom02 · Accepted solution]

We solved the problem as following:

Use the merge function to get the data from (in this case) three tables into one.

This provided that I knew for every value or it is a proceed or a cost.

Then I made a conditional column, which stated: if it is a proceed give zero, if it is a cost, give me the year from another column.

This new column I used as the key to link it to the date table.

By doing this, Proceeds is only given as a total, whereas Total Costs is divided by months.

[Tom02 · Accepted solution]

Regarding my specific wish to get 2024 divided by months and the other years not, can be accomplished by doing the following steps:

Make a date table, with years and months.

Then make a conditional column, IF Year equals 2024, then give Month, IF Year does not equal 2024, give 0.

Then merge the Year column and the conditional column. You get 20220, 20230, 20241 (so january 2024), 20242 and so on.

Then duplicate this merged column and replace values: change 20220 into 2022, 20230 into 2023 and for 2024: 20241 to 01/2024, 20242 to 02/2024 (for all the months of 2024).

Then you can use this last column in your matrix and it shows you 2024 divided by months and the others year in total.  

[Tom02]

@v-kaiyue-msft Thank you for your answer.

The table which contains the proceeds and costs comes from another table then the table which contains the values.

So the measure is not working, because the table for the proceeds and costs is a text table, which is linked eventually to a other table which contains the numbers.

This is how my report looks right now:

Maybe you know a measure, which is taken into account that the data comes from multiple tables?

Thank you in advance.

Regards,

Tom

[v-kaiyue-msft]

Hi @Tom02 ,

1. LOOKUPVALUE function, which can return the value of the rows that satisfy all the conditions specified by one or more search criteria. If there is a relationship between the table containing the result columns and the table containing the search columns, then in most cases, the use of the RELATED function (rather than LOOKUPVALUE) will be more efficient and can get better performance.

2. Relational functions, these functions are used to manage and exploit relationships between tables. For more details refer to the link: Relationship functions (DAX) - DAX | Microsoft Learn.

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

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

[Tom02]

Hi, @v-kaiyue-msft 

Sorry for my late response,

In het meantime I changed the report.

However, I still did not succeed to make it how I wanted it to be.

So, the column x has two values, which are divided by the matrix. For the Total Costs the way it is presented now is fine, but for the Proceeds it shouldn't be divided by any year or month; just the total.

I can't use the LOOKUPVALUE because of this: the records with the values are coming from one table, then I have a date table (in which the periods are made as shown beneath). A third table contains or  someting is a cost or a proceed (but does not contain dates or numeric values, so the function will not work in my case). These three tables are related to each other.  

I hope that I informed you better now.

Thanks in advance,

Tom

[v-kaiyue-msft]

Hi @Tom02 ,

Not quite sure what your original data looks like, so I'll do some calculations based on this example data.

1. create the MEASURES and calculate the total returns ignoring the years and months.

Proceeds =
CALCULATE(SUM('financials'[ Sales]),ALL(financials))

2. create MEASURE to calculate the cost by year and month.

Costs =
CALCULATE(SUM(financials[ Sales]),FILTER(ALL(financials), YEAR('financials'[Date]) = YEAR(MAX('financials'[Date])) && MONTH('financials' [Date]) = MONTH(MAX('financials'[Date])))))

There are also some aggregation functions that can be utilised in the matrix, which will aggregate based on the current context of the matrix, which you can tweak to suit your needs.

If the current data does not meet your expectations, you can provide relevant example data to better help you solve the problem.

Best Regards,

Clara Gong

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