Using a table to show share by time period, yoy, and customer(s)

[rrjr007]

Greetings everyone.  I'm trying to use a table to do the following:

1 - show share for a time period(s)

2 - compare the share for two time periods, and visually show if it is up, down, or flat

3-  compare the share for two time periods, and visually show if it is up, down, or flat, this time adding customers into the mix

For question 1, When I use the 100% stacked column chart or the matrix, I get the percentage share I want (see below).  However I cannot figure out how to get this view in a table.

Below is a sample of my data.  I'm sure this is a series of measure I'll need to do, I just cannot figure it out.  Thank you.

[bhagyashripatil]

Hello @rrjr007 ,

Please find below the DAX you need to create to get aggregated % values for the Yearly measure values, which you are referring to.

1) Yearly_Total = CALCULATE( SUM( Table[measure] ), ALLEXCEPT( Table, Table[Date].[Year] ) )

2) %Yearly_Total = SUM( Table[measure] ) / [Yearly_Total]

Also attaching the screenshot for reference.

Note: I have tried this on other sample data.

[rrjr007]

@bhagyashripatil  I tried the reccomended formula:

Yearly_Total = CALCULATE(SUM( Data[Std Cnt]), ALLEXCEPT(Data,Data[Fiscal YTD].[2013]))

I received the error below.  Thoughts?  Thanks.

[bhagyashripatil]

Hello @rrjr007 ,

A slight change needed in your DAX, which is as follows:

Yearly_Total = CALCULATE(SUM( Data[Std Cnt]), ALLEXCEPT(Data,Data[Fiscal YTD].[2013]))

Please remove the part highlighted in RED in above DAX

Your Updated DAX : Yearly_Total = CALCULATE( SUM( Data[Std Cnt] ), ALLEXCEPT( Data,Data[Fiscal YTD] ) )

This sould solve your error.

For your information:

We dont pass the year value after referring the Year column "Data[Fiscal YTD].[2013]" , that we do in case of Date.

If you refer a Date column, that is where you need to give granularity of Year/Qtr/Month/Date etc. like -> Data[Fiscal Date].[Year]

If I answer your question, please mark my post as solution, this will also help others.

[rrjr007]

For the longest time, I was not getting this to work.  There were a few reasons.  The biggest one was a slicer.   Using your data as an example, I had Office Supplies de seleted in my slicer.  So, my totals were not matching.  Once i cleared the slicer, my totals and percentages are working perfectly.

The issue with the slicer, is leading me to a followup/new question.  How do I get the data to reflect the numbers when a slicer filters out a group - for example Office Supplies is deselected?

Thanks in advance.

[Greg_Deckler]

@rrjr007 - Not sure if you are still having an issue, but if you are and the problem is with a total line when you have a slicer selected (I *think* that is what you said) then you may have a measures total problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

[amitchandak]

@rrjr007 , Not very clear

In a Matrix - put Axis of stacked on Row and legend on the column