cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Segregating two outcomes based on a distinct value

Dear community,

hope you are fine.

 

I'm trying to segregate rows with common Year but with different values on Priority scale, to use on another table.

 

The rule should be: for a specific ID, if a certain Year has just Priority #1, then average its prices; if the Year has both Priorities #1 and #4, then exclude Priority #1 rows, and pick the price of Priority #4; if the Year just has Priority #4, then pick the price of Priority #4.

 

So my data sample is something like this below:

IDYEARPRIORITYPRICE
1202315
1202313
1202411
1202412
1202441
1202544
1202641

 

And the desired outcome would be:

IDYEARPRIORITYPRICE
1202314
1202441
1202544
1202641

 

Note: There will be several different IDs in the data model.

 

 

Any help, please?

 

Thanks a lot!

 

Regards

1 ACCEPTED SOLUTION
Kumail
Continued Contributor
Continued Contributor

Hello @Anonymous 

 

The solution is attached for your reference.

Kumail_0-1624465458277.png

I have created couple of measures and a table using dax based on the condition when 

-Priority = 4 -> keep the price same for the year.

-Prioirty = 1 -> average the price for the year.

Kumail_1-1624465596426.pngKumail_2-1624465637953.pngKumail_3-1624465664407.pngKumail_4-1624465680998.png

 

https://drive.google.com/file/d/17Vjx0hbq_wJltpGuZKuAhkmZ_QQv0Gn4/view?usp=sharing

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly and I would love to see your like.

 

Regards

Kumail Raza

 

View solution in original post

8 REPLIES 8
Kumail
Continued Contributor
Continued Contributor

Hello @Anonymous 

 

The solution is attached for your reference.

Kumail_0-1624465458277.png

I have created couple of measures and a table using dax based on the condition when 

-Priority = 4 -> keep the price same for the year.

-Prioirty = 1 -> average the price for the year.

Kumail_1-1624465596426.pngKumail_2-1624465637953.pngKumail_3-1624465664407.pngKumail_4-1624465680998.png

 

https://drive.google.com/file/d/17Vjx0hbq_wJltpGuZKuAhkmZ_QQv0Gn4/view?usp=sharing

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly and I would love to see your like.

 

Regards

Kumail Raza

 

View solution in original post

Anonymous
Not applicable

@Kumail Thanks for your help!

I can take as good your suggestion. 

 

Best regards

Kumail
Continued Contributor
Continued Contributor

Hello @Anonymous 

 

The solution is attached for your reference.

Kumail_0-1624465458277.png

I have created couple of measures and a table using dax based on the condition when 

-Priority = 4 -> keep the price same for the year.

-Prioirty = 1 -> average the price for the year.

Kumail_1-1624465596426.pngKumail_2-1624465637953.pngKumail_3-1624465664407.pngKumail_4-1624465680998.png

 

https://drive.google.com/file/d/17Vjx0hbq_wJltpGuZKuAhkmZ_QQv0Gn4/view?usp=sharing

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly and I would love to see your like.

 

Regards

Kumail Raza

 

Kumail
Continued Contributor
Continued Contributor

Hello @Anonymous 

 

If you could provide sample .pbix file, that would greatly help writing you a solution quickly.

 

Regards

Kumail Raza

Anonymous
Not applicable

Hi @Kumail! thanks for your interest!

Unforntunately this is a company reserved data model, I can not share the file, but the tables that I posted have the data I need.

 

Cheers

Kumail
Continued Contributor
Continued Contributor

@Anonymous You can just send the .pbix with the current table and I will get back to you. 

 

Regards

Kumail Raza

amitchandak
Super User
Super User

@Anonymous , Try a measure like


averageX(summarize(Table, Table[year], "_1", calculate(average(Table[PRICE]), Table[PRIORITY] =1), "_2", calculate(average(Table[PRICE]), Table[PRIORITY] =4)), if(not(isblank(_2)) && not(isblank(_1)), [_2],if(not(isblank(_2)),[_2],[_1] )))

 

take max for PRIORITY

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!
Anonymous
Not applicable

Hi @amitchandak! thanks for your tip. 

First, I forget to add the ID column in the sample tables, which may make any differences.

 

Don't know if it's related with that point that I mentioned above, but unfortunately your solution didn't work.

 

Do you have any clue on that?

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.