topic Re: Repeated Labels and Values Matrix table in DAX Commands and Tips

Repeated Labels and Values Matrix table

ELToninio — Thu, 24 Oct 2024 16:05:03 GMT

Hello,

I have 3 fact tables :

Cost per nature (All Data Table) ; Cost per runnings costs ; cost per subcategory of running cost

I have a hierarchy as followed :

Nature of Costs (1) : within I have Running Costs, Salaries etc...

Nature of Running Costs (2) (here is a focus on only running costs)

Subcategory of Running Costs (3)

First problem :

However when I build my matrix, with my formula I managed to make sure that Nature of Costs cannot be drilldown unless it is "Running Costs". When we go into the nature of running costs I manage to prevent drilldown from happening for values that are not equal to "Travel Expenses" but I wanted to add more values to the list aside of Travel Expenses, and i don't know how to do that. See my code below.

Second problem :

In my table matrix, all the subcategory of runnings labels are showing up into each Nature of Running Costs and I don't understand why there are not splitted in each Running costs they belong to. On top of that the amount of the Nature Of Running Costs subtotal is repeated in each row of the subcategory. How could I fix that ? Maybe it comes from my messy model ? (see below)

My formula

Running Cost per Category =

VAR TotalNatureOfCosts = sum('All Datas'[Costs YTD])

VAR TotalNatureOfRunningCosts = sum('Actuals Nature of Running Costs'[Costs YTD])

VAR TotalSubcategoryRunningCosts = sum('Actuals Sub Running Costs'[Costs YTD])

VAR FiltreNatureOfCosts = ISINSCOPE('Table Nature of Costs'[Nature of Costs])

VAR FiltreNatureOfRunning = isinscope('Nature of Running Costs'[Nature of running costs])

VAR FiltreSubcategoryRunning = isinscope('Actuals Sub Running Costs'[Subcategory of Running Cost])

Return IF(FiltreNatureOfRunning = true() && selectedvalue('Table Nature of Costs'[Nature of Costs]) <> "Running costs", blank(),

IF(FiltreSubcategoryRunning = true() && selectedvalue('Nature of Running Costs'[Nature of running costs]) <> "N07 - Travel expenses", blank(),

IF(FiltreNatureOfRunning = true(), TotalNatureOfRunningCosts,

IF(FiltreSubcategoryRunning = true (), TotalSubcategoryRunningCosts, TotalNatureOfCosts))))

My model :

thank you very much

Re: Repeated Labels and Values Matrix table

lbendlin — Thu, 24 Oct 2024 20:39:56 GMT

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information. Do not include anything that is unrelated to the issue or question.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Re: Repeated Labels and Values Matrix table

Anonymous — Mon, 28 Oct 2024 08:57:06 GMT

Thanks for the reply from lbendlin , please allow me to provide another insight:

Hi, ELToninio

1.Regarding your first question, you might consider using the NOT() and IN() functions, modifying it to the following example, which will allow you to add multiple values for evaluation:

IF( FiltreSubcategoryRunning = true() && NOT(SELECTEDVALUE('Nature of Running Costs'[Nature of running costs]) IN {"N07 - Travel expenses", "Other Value 1", "Other Value 2"}), BLANK(), ... )

2.As for your second question, the issue of subcategory labels appearing under each Nature of Running Costs and the repetition of subtotals typically stems from your relationship and model settings.

Firstly, you should check whether your relationship configurations are set up correctly.

Secondly, verify that the hierarchy settings in your matrix are accurate.

For further details, please refer to:

Dealing with Measure Totals - Microsoft Fabric Community
Measure Totals, The Final Word - Microsoft Fabric Community
Solved: No total value for measure - Microsoft Fabric Community

In this regard, we suggest that you upload some sample data for us, as this will facilitate a more specific analysis of the issues at hand.

When uploading a file, please be careful to delete sensitive information.For questions about uploading data, you can try the following links:

How to provide sample data in the Power BI Forum - Microsoft Fabric Community

Solved: How to upload PBI in Community - Microsoft Fabric Community

Best Regards,

Leroy Lu

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