Urgent!! Dax Help Required For calculation

Hi Anonymous ,

Based on your description and your sample, you are trying to determine whether site is continuously declining or not by Sales_Decrease, I made some attempts and you can check the following results:

Result = var _r=SUMX(ALLSELECTED(Degrowing_TB[Month/Year]),[Sales_Decrease])
return IF(_r<>[site_Count],1,0)

Just use this measure and apply it to the VISUAL selection value of 0

Hope it helps!

Best regards,
Community Support Team_ Scott Chang

Hi Anonymous 

The result if you have provided is wrong, i think you haven't check the correctly what is my requirement. 

----Based on your description and your sample, you are trying to determine whether site is continuously declining or not by Sales_Decrease-----

-->My Requirement is to get only those site which is continously sales went down on those month selection dynamically.

Example :- Let say i have selected from Apr 23 to Aug 23 in my slicer so total month will be 5(Count of month)
Then i need to now find out only those site which sales was continous down in these 5 month. other site we don't want in table.

LIke this if i select 6 month or 8 month then in table chart only those site need to show which was continous down 6 times or 8 times.

Your result is not correct at all.

If you check my pbix file then there if you will select 4 month, 6 month or 7 month you will get the site_count 4, 6, 7 mean those sites are continous down by these times but rest data is coming which we don't want.

Anonymous 

Your result is containing those sites which didn't fall into the degrowing of that month selection. Try selcting 5, 6  and 7 month count then check the result.

Hi Anonymous ,

I may have misunderstood, please provide your expected results, thanks.

Best regards,
Community Support Team_ Scott Chang

Hi @ v-tianyich-msft

Let's say i have selected month Apr to Oct 23 then the total month count is 7 and you can check those sites are coming in my table colum(Site_count) 7 mean these sites sales have been down in these all selected month. which is result is coming correct.

But aswell as 7 you can check in column Site_count those sites are coming where sales were down 6 times, if you will scroll down then you will get 5 times, 4 times also record

So we want to filter this that if i select 7 month then site_count column should contain only 7 times down site not others.

If you have any issue you can share your available time for a short call and we can discuss over this. 

Hi Anonymous ,

Based on your description, I think the answer I initially provided you was correct because there were so many sites that I sifted through 12090 as an example. For the first graph I chose April through June and they are all gradually declining. For the second graph, I chose April through July, and the table data is missing because July is boosted compared to June, so it's not being displayed.

Best regards,
Community Support Team_ Scott Chang

Hello Anonymous!

Here is my solution:

1. In Power Query, I changed the type of "site" to text, sorted ascending by site and then ascending by month/year; then I added an index column.

2. I updated measure TOtal_month_selected with an AllSelected statement:

3. I added a calculated column called Decrease, with the following code:

4. I created the final measure Continuous Descent that displays only the sites that had a continuous decrease in the selected months:

This solution gives the desired results:

Here is my pbix: https://drive.google.com/file/d/1uWeCCSI2lz-mr-C9dh80YSiVAgrnYanr/view?usp=sharing

Let me know how it goes!

Cheers!

Cristi

Hi @Links & Cristi 

thankyou so much for this insight, it was exact as i was expecting the result.

By chance do you have 2 mintues to connect as there is some challenges i am facing in my another data regading this one only. 

Just two mintues. 

Thanks for your consideration. 

Regards

Ashish Tiwari

Hi,

Thanks for the feedback.

Best regards,
Community Support Team_ Scott Chang