Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Create EBITDA scheme with DAX (Matrix visual)

Hey everyone! 

 

I want to recreate the scheme to calcute the EBITDA or EBIT. 

 

 

You have subcategories like revenue, interest & depreciation and also main categories like EBIT & gross profit. 

 

I made an excel file to link the accounts of the general ledger (from Nav/Dynamics 365) to the subcategories. 

For example:

70000 = revenue

60000 = COGS

 

If I make two measures, (even tried totals), if give this as result:

Anybody have any idea to do this the most efficient way. I want to put the whole scheme in 1 matrix like the last column. 

 

Thanks a lot in advance. 

Need Help

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Anonymous , The information you have provided is not making the problem clear to me. Can you please explain with an example.

     

    Two guesses  I have

    You need to split measure into dimension and measure. measure you need a table for disply

     

    example

    union(
    summarize('Table',"Measure","sales","This period",[SALES YTD],"Last period",[SALES LYTD],"POP",[SALES YOY])
    summarize('Table',"Measure","unit","This period",[unit YTD],"Last period",[unit LYTD],"POP",[unit YOY])
    )

    you are looking for a Hybrid display with Matrix Column and measure
    https://community.powerbi.com/t5/Community-Blog/Creating-a-custom-or-hybrid-matrix-in-PowerBI/ba-p/1354591
    https://community.powerbi.com/t5/Quick-Measures-Gallery/The-New-Hotness-Custom-Matrix-Hierarchy/m-p/963588#M428

    vote for Hybrid Table
    https://ideas.powerbi.com/ideas/idea/?ideaid=9bc32b23-1eb1-4e74-8b34-349887b37ebc

     

     


    Appreciate your Kudos.


    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      amitchandak @ amitchandak

      I made this in Excel and transfered it to PowerBi. The values without a specifiek measures are 'Direct Sales Stores' and the some rows are with specific measures like 'EBITDA'. 

       

      I tried, after reading the articles, the next: (Amount_rapportering is were the values are, Grootboek[sub] is the table from Excel. 

       

       

      Poging 1 = 
CALCULATE([Amount_Rapportering], Grootboek[Sub] = "Inkomst")
      Poging 2 = 
CALCULATE([Amount_Rapportering], Grootboek[Sub] = "Extra Inkomst")

       

       

       

      calculate the Gross Margin

       

       

       

      Gross Margin = 
var Inkomst = CALCULATE([Amount_Rapportering], Grootboek[Sub] = "Inkomst")
var Extra   = CALCULATE([Amount_Rapportering], Grootboek[Sub] = "Extra inkomst")

return
Inkomst + Extra

       

       

       

       

       

      Komop = 
var Currentitem  = SELECTEDVALUE(Blad2[Naam])

return
SWITCH( TRUE(),
    Currentitem = "Gross Margin",DIVIDE([Gross Margin], 1, 0),

calculate ([Amount_Rapportering], 
    FILTER(Blad2, Blad2[Naam] = Currentitem)))

       

       

       

      What happend after I use the new measure 'Gross Margin' in the matrix. It didn't work. Any idea why? 

       

      • v-easonf-msft's avatar
        v-easonf-msft
        Community Support
        5 years ago

        Hi, Anonymous 

        I'm a little confused by your description. Can you explain bit more?

        A simplified  pbix file can better help us understand the problem.

        Best Regards,
        Community Support Team _ Eason