Calculating % of total YTD Sales

[Anonymous]

Hi All, 

Im looking to calculate % of Total for YTD Sales, expecting to look something like this:

Expense RollUp	Current Month	   YTD	    YTD % of Total
Vendor A	 $1,545,680 	 $5,481,273 	3%
Vendor B	 $403,425 	 $2,519,656 	1%
Vendor C      $363,462 	 $2,110,283 	1%

Im able to calculate everything correctly but the % of total. Whenever i go to create a measure, it just gives me the percent as 100 (calculating the actual YTD against itself). How can i create a global measure to calculate against ALL sales for the given year? 

[amitchandak]

@Anonymous , What I getting that you need % of MTD over Ytd Or complete year

These will help

examples

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)))
last year MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH))))
Month behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Month))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))


YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184


Appreciate your Kudos.

[Anonymous]

What you really want to calculate is totally unclear. Please create a simple meaningful calculation in a table or a matrix and post it.

For instance, what does it mean "current month" in your table? What does it really mean "YTD"? Since there are no points of orientation in the table (no dates, no months on rows/columns), no one can be sure what it is you want. YTD % of Total? Of which Total?

If you make your question fully understandable, then I'm sure the answer will be simpler than you think.

Best
D

[foomanschu13]

The documentation on removefilters seems to discuss your situation and might be what you're looking for.  Ideally you would need a measure that calculates the grand total YTD sales and then divide your YTD measure against it.  You can test by first creating the grand total and making sure it appears the same no matter what dimension (i.e. Vendor) is added.

https://docs.microsoft.com/en-us/dax/removefilters-function-dax

[Anonymous]

@foomanschu13 This is very helpful, unfortunately its now giving me complete totals across all years in my data. I want to be able to compare it to the Year's total. Do you have any suggestions? This is a great step in the right direction, though. Thanks!

[foomanschu13]

I believe you need to still use the DATESYTD in your measure somewhere

something like

YTD Revenue = CALCULATE(SUM([Sales Amount],DATESYTD('Date'[Date]))

YTD GT Revenue = CALCULATE([YTD Revenue],REMOVEFILTERS())

YTD % = [YTD Revenue] / [YTD GT Revenue]

That seems to work for me and you don't need anything in the REMOVEFILTERS unless you specifically want to. Depending on what you want to ultimately see, ALLSELECTED may also be what you want instead of REMOVEFILTERS