Sort Order Impacting Measures

[Anonymous]

I have a dataset which has This Year and Year Ago data. I used a table to provide the sorting order for the same using a relationship and relation function. Post this I sorted TY/YA columns based on the new column with sort order. But as soon as I did that my change measures (TY/YA-1) stopped working and started giving me error. Has anyone faced a situation like this?

[dax]

Hi @Anonymous , 

I am not clear about your requirement and your data structure, if possible could you please inform me more detailed information(such as your expected output and your sample data)? Then I will help you more correctly.

Please do mask sensitive data before uploading.

Thanks for your understanding and support.
Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

My sample data looks like this:

Time Period	Brand	Sales
TY	B1	100
YA	B1	100
YA	B2	1110
TY	B2	1100

Hi

Apologies for the bad explanation of the problem

Situation:

I was using a measure to calculate change in sales: Sales(TY)/Sales(YA)-1. It was working perfectly.

Problem:

However, when I was plotting a clustered column chart with brands in axis and TY/YA as legends and value. I couldn't control the TY/YA.

Hence, I created a table with index of TY as 2 and YA as 1. I used related function to bring the index values to the sales table after creating relationship on Time Period. Then I sorted column Time Period on basis of Index. This is when my change measures stopped working and started giving me error.

I am a newbie in DAX, please can you help me by pointing out what did I do wrong in this case?

[dax]

Hi @Anonymous , 

I think this can't sort by legend column(it seems to be related to value and axis ). In addition, if possible, could you please inform me your expected output? Then I will help you more correctly.

Please do mask sensitive data before uploading.

Thanks for your understanding and support.
Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

I want to bring change the order of legends from TY/YA to YA/TY. Hence, I had used the re-ordering using a index column. However, as soon as I brought in the index column and sorted the TY/YA column basis index my change measures stopped working and gave error.

[dax]

Hi @Anonymous , 

I don't know your error when you use legend index, I test this in my environment, I create relationship between them , and set sort by "ORDER", it works.

So I don't know  which cause this problem. If possible, could you please inform me your sample data or pbix(recommend to use OneDrive for Business, and you could use virtual data instead of real data)

Please do mask sensitive data before uploading.

Thanks for your understanding and support.
Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Can you try to see if this DAX works in your case:

Chg Value Share = IFERROR((calculate(sum(Data[Sales]),Data[Time Period]="TY")/
(calculate(sum(Data[Value Sales]),Data[Time Period]="YA"))-1,"")

[amitchandak]

@Anonymous , how is TY, LY created. Are they created using date calendar and common time intelligence? Using datesytd, totalytd. Example

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"12/31"))
This Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD((ENDOFYEAR('Date'[Date])),"12/31"))

Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
Last YTD complete 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/

[Greg_Deckler]

Not at all clear, Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490