Urgent!! Dax Help Required For calculation

[Anonymous]

Hi @Links @Team @PBCommunity @PBICommunity 
Issue :- 

I have site, month/year, Sales and region head column in my Table.

Now i have to find out  those sites where the sales are going continous down month on month basis based on my month selction dynamically. 

Let's say : I have selected 4 month from slicer then i need those site which have been continous sales down for those selected month.

or i have selected 6 month or 8 month then i would be needing those site which sales continous down for those selected 6 or 8 month.

Below is the link for my PBIX file where you can access the PBIX file for that you would be needing PBIX report server.

https://drive.google.com/file/d/16OM7wFp84AU0a_wktGU6bsMTlv8YxzTB/view?usp=drive_link

Also attaching the excel data which i have used in this PBIX file.

https://docs.google.com/spreadsheets/d/1J0St32wZ3ZqjlQJ8zMkGApAhN8Kl8Zuq/edit?usp=drive_link&ouid=10...

In first page i have tried my own formula you can understand and you can try your own method on another page.

Thankyou all for your help.

Regards
Ashish Tiwari

[Anonymous]

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

[Anonymous]

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]

@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.

[Anonymous]

Hi @Anonymous ,

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

Best regards,
Community Support Team_ Scott Chang

[Anonymous]

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. 

[Anonymous]

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

[Links]

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

[Anonymous]

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

[Anonymous]

Hi,

Thanks for the feedback.

Best regards,
Community Support Team_ Scott Chang