P&L Ragged Hierarchy With Custom Measures Within

[Anonymous]

Hello,

I'm building a P&L report and I need to show some custom resumes in specific lines.

For example, I need the value 1.847,33 (Which is already a mesure) to be showed in the line labeled as "(+) DEPRECIAÇÃO/AMORTIZAÇÃO".

I tried puting a column (Subtotal) on my main structure db (d_Plano de Contas), where the "(+) DEPRECIAÇÃO/AMORTIZAÇÃO" has a number 1 while all the other lines has 0, and than using a new measure "Realizado Real" (As you can see bellow), but it did not work.

Can you guys please help me to figure it out ?

Thanks in advance.

[Anonymous]

Hello again,

I tried something new, and I think it might be close to a solution.

I have a column identifying each subtotal with a diferent number, in this case the number 2 represent my matrix row "(+)Depreciação / Amortização".

I was sucessful identifying the lines I want to assing a custom calculation, but as you can see in column "Medida" bellow the calculate expression (Which I want to result in -1.847,33, as in the column "Amort/Deprec" total) do not show in the "(+)Depreciação / Amortização" row. How can I manage it to appear in the correct row, and also respect any matrix slicers ?

Thanks in advance.

[v-shex-msft]

Hi @Anonymous,

It sounds like you want to modify the display categories and add custom values and do summarize with raw table records, right?

If this is a case, I'd like to suggest you extract raw table category field values and union new values that you mentioned with a specific index on it.

Custom Sorting in Power BI 

After these steps, you can use the new table category field to replace the old one on the matrix and write measures to lookup values based on the current category, and display calculated results on added new category values.

If you not so clear on how to check the current row content level, you can refer to the following blog and add if statement to check the current row content value and replace the display value if it meets the specific value.

Clever Hierarchy Handling in DAX 
Regards,

Xiaoxin Sheng

[Anonymous]

Hi Amit,

No problem. I can also share a .pbix, which I think is a better option. If you have an address for that (email, drive..) let me know. 

I just read the Kasperonbi article about "isinscope", and I think I get the idea, but I still not able to solve my problem, maybe because of my model relationship. 

I got to this point, where I can identify the scope that I want to make custom calculations, but I still don't know how to differentiate each line in the scope, so I can assign each calculus.

The Kasperonbi example uses a IsTotal column, but as far as I understand, it'll help when you have the same custom calculation for the whole scope.

[amitchandak]

@Anonymous , Can you share sample data and sample output in table format?

Isinscope can help but need to check based on data :https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/

Also, there is a good solution Traditional FinancialStatements, refer if that can help

https://community.powerbi.com/t5/Desktop/Traditional-Financial-Statements/td-p/7223