Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
danielpcamara
Helper I
Helper I

Custom Subtotals for Income Statement

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:

Capturar.PNG

 

mFormulasIS:

Capturar2.PNG

 

Relationships:

Capturar3.PNG

 

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.

3 REPLIES 3
danielpcamara
Helper I
Helper I

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:

Sem título.png

 

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?

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

@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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.