DAX to calculate % of Rev

[Sunila]

Hi,

I need assistance in creating a DAX formula to calculate the percentage of Actual Revenue by Category. This formula should only consider the Sales amount (not the grand total) when calculating the percentage. In other words, all values in the Actual Revenue column should be divided by their respective Sales figures. For example, the % of Actual Revenue for Sales should be 100%, and for Cost of Goods Sold, it should be 1.54% (153060.55 divided by 99347513.19), and so on. In Excel, achieving this is straightforward by using an absolute cell reference ($B$2) to keep a constant denominator. However, I'm unsure how to achieve the same result in DAX. Please provide guidance on how to accomplish this

[DataNinja777 · Accepted solution]

Hi @Sunila 

I recreated your financial statements table (except for the gross margin, EBIT and Total (Net Profit) which don't just follow the mapping aggregation naturally,) and produced your requried output as follows. (Your dummy data has a spectacular profitability, BTW).  

I attach the pbix file in the link below:

% of Sales.pbix

[Sunila]

It worked, Thank you so much @DataNinja777  

I have an additional question. I want to add Last year column as well here. So if you assume current data is for current year, report also need to show last year data for all category in 'Last Year' column. Can you possibly advice how to get the Last year data ?

[DataNinja777]

You are welcome @Sunila 

I am assuming that you are downloading your raw data from your ERP system in for example trial balance format with date field and GL account field information.  I would combine the different time period trial balance information in one fact table like movement of trial balance (monthly or yearly) using power query, and then create a separate calendar table and mapping table and create relationships with your fact table  in a Power BI data model to do the analysis you require. 

[Sunila]

@DataNinja777 Can we use use DAX function instead, may be PreviousYear to get the last year data in LY column? 

[DataNinja777]

Hi @Sunila,

Certainly.  But is your data model set up in that way?  Do you have a calendar table and the fact table with all the years' numbers properly set up in your data model?  Then, of course you can use dax to do the time intelligence calculation you require.  

[Sunila]

@DataNinja777 It is set up in my data model. I am using the below DAX for last year column but it is not fetching any data. Highly appreciate if you can advise on it.

Last Year =

Var MaxDate = Max(GeneralJournalAccountEntryView[ACCOUNTINGDATE])

VAR LastYearStart = DATE(Year(MaxDate)-1, Month(MaxDate),Day(MaxDate))

VAR LastYearEnd = DATE(Year(MaxDate)-1, Month(MaxDate),Day(MaxDate))

RETURN

SUMX(

    FILTER(

        GeneralJournalAccountEntryView,

        GeneralJournalAccountEntryView[ACCOUNTINGDATE] >= LastYearStart &&

        GeneralJournalAccountEntryView[ACCOUNTINGDATE] <= LastYearEnd

    ),

   GeneralJournalAccountEntryView[ActualAmount]

)

[DataNinja777 · Accepted solution]

Hi @Sunila 

I cannot see your exact data model, but I would be referencing calendar table date field instead of referencing fact table date field like you wrote above: Max(GeneralJournalAccountEntryView[ACCOUNTINGDATE]).

Always referencing and bringing in dimension table fields in visualization helps power BI to slice and dice the information in different tables which has common fields with relationships.  For demonstration, I created a dummy 2022 year data (assuming your original numbers are for 2023), and created a calendar table and relationship with your fact table.   

Just bringing in the year field from your calender table which has relationship with your fact table will enable you to generate a visualization like below:

I attach the link to the pbix sample file below:

% of Sales (with time dimension).pbix

[DataNinja777 · Accepted solution]

Hi @Sunila 

I recreated your financial statements table (except for the gross margin, EBIT and Total (Net Profit) which don't just follow the mapping aggregation naturally,) and produced your requried output as follows. (Your dummy data has a spectacular profitability, BTW).  

I attach the pbix file in the link below:

% of Sales.pbix