Forum Discussion

Rygaard's avatar
Rygaard
Icon for Resolver I rankResolver I
5 years ago
Solved

Fill out Blank Values with another Calculation in a Matrix (Subtotals for EBITDA and EBIT)

I have to make a Classick proffit loss table - this sounds easy

BUT i need some sums in the middle of my matrix

TLDR;

I want the red part substituede with a sum of Actual(auto)  where the account number is in the range.

 

So i made an Index table (for sorting purposes) and maped all our account numbers (600)  to this index table

and that is working great.

 

BUT EBITDA is not a specifik account, and it treverses the categories i have in the index, so it is blank in the matrix.

 

Now i would like to calculate if isblank  - and i have the range specified in my index table.. but i cannot get this to work it keep on beeing blank - im pretty sure this is a filtering problem, so i tried using All()  .. perhaps wrong...

 

As you can see on this picture i have made most of it work, and i can make it show the Range i want to sum, in stead of blanks.. 

 

 

There is ofc. in the "finansRecords table" acount numbers  so i bassicly want a sum of the numbers between 10000 and 59998 for My EBIT and so on .

 

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Rygaard ,

     

    First you create the following measure

    Measure = -1 * CALCULATE ( SUM ( 'FinansRecords'[Auto] ) ) / 1000

    Then create the another measure 

    'Actual with Profit & loss category =
IF (
    ISBLANK ( [MEASURE] ),
    SUMX ( ALL ( 'FinansRecord' ), [Measure] ),
    [Measure]
)

     

    Best Regards,

    Stephen Tao

     

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

  • Rygaard's avatar
    Rygaard
    5 years ago

    This is gettign abit strange, with no connection between  the table hosting the ranges and no extra calculation i managed it.

     

    it seems powerbi will fill out the first level based on my non connected support table based on my within range

    and then then here comes the strange part it will arrange the correct accounts under that headline, this i can not fatom how it is doing but it works...

    The formular i endet up using:

     

    'Profitlossgroupe totals = CALCULATE(SUM(FinansRecords[Auto]),ALL(FinansRecords[F.G/LNo_AsNumber]),
FILTER(FinansRecords,(FinansRecords[F.G/LNo_AsNumber]>=MAX('Index$ (2)'[from]))),FILTER(FinansRecords,(FinansRecords[F.G/LNo_AsNumber]<=MAX('Index$ (2)'[to]))))*-1/1000

     

     

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Rygaard ,

     

    First you create the following measure

    Measure = -1 * CALCULATE ( SUM ( 'FinansRecords'[Auto] ) ) / 1000

    Then create the another measure 

    'Actual with Profit & loss category =
IF (
    ISBLANK ( [MEASURE] ),
    SUMX ( ALL ( 'FinansRecord' ), [Measure] ),
    [Measure]
)

     

    Best Regards,

    Stephen Tao

     

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

  • Rygaard's avatar
    Rygaard
    Icon for Resolver I rankResolver I
    5 years ago

    This is gettign abit strange, with no connection between  the table hosting the ranges and no extra calculation i managed it.

     

    it seems powerbi will fill out the first level based on my non connected support table based on my within range

    and then then here comes the strange part it will arrange the correct accounts under that headline, this i can not fatom how it is doing but it works...

    The formular i endet up using:

     

    'Profitlossgroupe totals = CALCULATE(SUM(FinansRecords[Auto]),ALL(FinansRecords[F.G/LNo_AsNumber]),
FILTER(FinansRecords,(FinansRecords[F.G/LNo_AsNumber]>=MAX('Index$ (2)'[from]))),FILTER(FinansRecords,(FinansRecords[F.G/LNo_AsNumber]<=MAX('Index$ (2)'[to]))))*-1/1000