Forum Discussion

danielpcamara's avatar
danielpcamara
Resolver I
7 years ago

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 ...
Need Help
danielpcamara's avatar
danielpcamara
Resolver I
7 years ago

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's avatar
v-juanli-msft
Community Support
7 years ago

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's avatar
    danielpcamara
    Resolver I
    7 years ago

    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.