Divide for Sales Percentage

DAX DAX Calculations
1

Running into an issue while i try to calculate a Sales percentage. Seem simple, but i am missing something here as my % values are showing 0.
My table contains different expenses and the Sales numbers in a table.

Need the visual to show the totals of expenses and the % of expense to the Sales

Calculated the measure to get the total of Sales
Sales = CALCULATE(SUM(‘Table’[Amount]),FILTER(‘Table’,‘Table’[Account] = “Sales”))
% of sales = divide([Total Amount],[Sales],0)

Both numerator & denominator are greater than 0. I tried changing to percentage but still show 0 for the calculation of 95325 / 616979 instaed of 0.15%

image

https://drive.google.com/file/d/1AJZpwtw_235a9Qtt4NqcDroqJyon-OTR/view?usp=sharing

2

I renamed your Table “Table” Sales.

When you place your [Sales] measure in the table visual it will return only BLANK values.That’s because in the table visual the Sales table get’s filtered down to only the Account visible in that context. You can overcome that by introducing ALL(‘Sales’) but you also have to account for the Date selection ALLSELECTED( ‘Date’ ).

Sales v2= 
CALCULATE( SUM('Sales'[Amount] ),
    FILTER( ALL('Sales'),'Sales'[Account] = "Sales"),
    ALLSELECTED( 'Date' )
)

With this result.
image

I hope this is helpful.

1 Like
3

Thanks @Melissa . Is there an alternative by not using the date? My concern is i have the fiscal periods from May to April. This table date is not a reliable value for me as the date is actually the date of the transaction rather than the period it belongs to. For example even the transactions posted in May of 2020, will be tied to fiscal year 2020 of period 12.
With my slicer for Fiscal Year, i couldn’t get the total sales correct.

4

In my view, you will have deal with the assignment to the correct period in your fact table so you can link that field to the Date table. There’s no scenario where you can get around the need for a Date table if your dataset contains a date field.

5

Thanks @Melissa for that suggestion.
I used a summary table for calendar to bring in the last date for the period with a look up. But i see that my total for the sales is picking all the data instead of the selected fiscal year. What am i missing here.

Sales Total =
CALCULATE( SUM(‘Sales’[Amount] ),
FILTER( ALL(‘Sales’),‘Sales’[Account] = “Sales”),
ALLSELECTED( ‘Sales’[Period end Date] )
)

Model attached -

image

6

Hello @train,

You can try using the below formula -

Sales Total = 
CALCULATE( SUM('Sales'[Amount] ),
    'Sales'[Account] = "Sales")

I’m attaching the screenshot as well as the PBIX file for the reference.

Hoping this is useful and helps you to achieve the desired analysis.

Thanks & Warm Regards,
Harsh

Sales Total
Sales Total790×286 21.3 KB

Sales percent (1).pbix (136.3 KB)

7

Hi @train, we’ve noticed that no response has been received from you since the 27th of June. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!