Product volume as a % of the Current Year total Volume, then report % change over last year

[bmccabe_PCS]

He is my beginner atempt at this. My issue is I can't retrieve the % of year total as a number. I am using the show as % of column total. How would I get the % of total volume per product as a decimal number I can do the simple math on?

Here is an attemt made to get this years % and compare to last years % of last years total but I get a % that is not correct.

My VOLUME column shown as a % of Column Total gives me 26.22% for one product in 2019. The product % of Column Total for 2020 is 25.93%. I want to show -0.29% change in the % of TOTAL VOLUME sold YoY.

2019 % of TOTAL VOLUME = 26.22%

2020 % of TOTAL VOLUME = 25.93%

% Change of TOTAL VOLUME YoY = -0.29%

With the below Growth Trend measure I get -8.51% which is wrong because the underneath data is numbers not %.

Growth Trend =

IF(

    ISFILTERED('Imported_Data'[MONTH_DATE]),

    ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),

    VAR __PREV_YEAR =

        CALCULATE(

            SUM('Imported_Data'[VOLUME]),

            DATEADD('Imported_Data'[MONTH_DATE].[Date], -1, YEAR)

        )

    RETURN

        DIVIDE(SUM('Imported_Data'[VOLUME]) - __PREV_YEAR, __PREV_YEAR)

)

Any assistance/advice would be appreciated. 

[v-jayw-msft · Accepted solution]

Hi @bmccabe_PCS ,

Please refer the measure.

Measure = 
var _current = CALCULATE(SUM('Table'[value]),ALLEXCEPT('Table','Table'[year],'Table'[product]))/CALCULATE(SUM('Table'[value]),ALLEXCEPT('Table','Table'[year]))
var _last = CALCULATE(SUM('Table'[value]),FILTER(ALLEXCEPT('Table','Table'[product]),'Table'[year]=SELECTEDVALUE('Table'[year])-1))/CALCULATE(SUM('Table'[value]),FILTER(ALL('Table'),'Table'[year]=SELECTEDVALUE('Table'[year])-1))
return
_current-_last

Best Regards,

Jay

[bmccabe_PCS]

I am getting an error in the Calculate expressions that the second pat of the calculate expression is not valid. 

I have created and added a DATE table to the project. I was able to get the YEAR Totals for each product.

Now I need to show the % of YEAR Totals that each product represents. I get a circular reference when trying to CALCULATE the YEAR totals which I need to calculate the % the total of the Year total for each grouped product and each year:

[amitchandak]

@bmccabe_PCS , it should be something like this

CALCULATE(
divide(SUM('Imported_Data'[VOLUME]),calculate(SUM('Imported_Data'[VOLUME]), allselected('Imported_Data'))),
)
-
CALCULATE(
divide(SUM('Imported_Data'[VOLUME]),calculate(SUM('Imported_Data'[VOLUME]), allselected('Imported_Data'))),
DATEADD('Imported_Data'[MONTH_DATE].[Date], -1, YEAR)
)

% of total only subtract

You can also try with datesytd

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA

[bmccabe_PCS]

Thank you for this! I will test it out and report back. Sorry was not around for a while:)