Custom Subtotals for Income Statement

[danielpcamara]

Hello everybody,
I did a lot of research, and read a lot of topics saying that this is not possible on power BI, but I'm stubborn. I really want to believe that this is possible. So, let start:

In the link bellow you will find two files, one xlsx file simulating my data allready formating, and a pbix file with the begining of my code.

Link: https://controlejundiai-my.sharepoint.com/:f:/g/personal/daniel_camara_controlejundiai_com_br/EmqoCV...

My data has the tables:
*dGeneralJournal: values x date x Account
*mAccounts: Account X Groups X Account of Income Statement
*mIncomesSatement: Account of Income Statement X Groups X Type
*mFormulasIS: Account of Income Statement where the type = Formula X Account of Income Statement that compose that Subtotals.

The secret to make this work is the column Type in the table mIncomeStatement and the Table mFormulasIS. Here is an print of the table if the link does not work:

mIncomeStatement:

mFormulasIS:

Relationships:

What I want is to create an "Custom Subtotal" to calculate the EBITIDA in my Income Statement Report. For accomplish that, I think that I need to use the formula IF to apply diferents formulas for the two types on my table "mIncomesSatement". And in the Case Type=Formula I need to use the CrossJoin Function to get the correct Sum, but I don't know how to do that:

This is my DAX code so far:

ISFormula = 
IF(
    SELECTEDVALUE(mIncomesSatement[Type])="Formula";
    "XXXXXXXX";
    CALCULATE(
        SUM(dGeneralJournal[Value]) *(-1)
    )
)

Thanks.

[danielpcamara]

So, I find a ugly solution, that don't allow drill through.
I create this table:

Teste = 
VAR TbRelated = ADDCOLUMNS(dGeneralJournal;"Id of IS";RELATED(mAccounts[IdISA]))
VAR TbCross = FILTER(CROSSJOIN(TbRelated;mFormulasIS); [Id of IS] = [IdISA Calc])
Return
TbCross

And Set up my Relationship like this:

And my Measure stay like this:

IS = 
VAR Normals = 
    CALCULATE(
        SUM(dGeneralJournal[Value])
    )

VAR Formula =
    CALCULATE(
        SUM(Teste[Value]);
        USERELATIONSHIP(Teste[IdISA];mIncomesSatement[IdISA])
    )

VAR Logical = 
IF(
    SELECTEDVALUE(mIncomesSatement[Type])="Formula";
    Formula;
    Normals
)
Return
Logical * (-1)

In the same like as my original post I put my solution. Is there a way to make this work with drill through?

[v-juanli-msft]

Hi 

Thank you for sharing your solution, i would suggest you review this article to learn more about drill through feature, if you have any question implement this, i'm happy to help you.

https://docs.microsoft.com/en-us/power-bi/desktop-drillthrough

(i'm going on weekend and away from office, i will work on your problem as soon as i come back)

Best Regards

Maggie

[danielpcamara]

@v-juanli-msft, thanks for the reply,

My problem is not with the drillthough it is self, the problem is that my measure has an "if" to use different ways to calculate. When my Income Statement Account is an subtotal, my formula Uses sum a calculated table that makes reference to my actual General Journal table, thus when I drillthough from a subtotal account is not possible to see the entrys that compose this subtotal, sinze the DrillThough page does not show this calculated table.