cancel
Showing results for
Did you mean:
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:

 ID YEAR PRIORITY PRICE 1 2023 1 5 1 2023 1 3 1 2024 1 1 1 2024 1 2 1 2024 4 1 1 2025 4 4 1 2026 4 1

And the desired outcome would be:

 ID YEAR PRIORITY PRICE 1 2023 1 4 1 2024 4 1 1 2025 4 4 1 2026 4 1

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

Thanks a lot!

Regards

1 ACCEPTED SOLUTION
Continued Contributor

Hello @Anonymous

The solution is attached for your reference.

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.

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

8 REPLIES 8
Continued Contributor

Hello @Anonymous

The solution is attached for your reference.

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.

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

Anonymous
Not applicable

I can take as good your suggestion.

Best regards

Continued Contributor

Hello @Anonymous

The solution is attached for your reference.

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.

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

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

Continued Contributor

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

Regards

Kumail Raza

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

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?

Announcements

#### Launching new user group features

Learn how to create your own user groups today!