Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Ttaylor9870
Helper III
Helper III

Assigning Values based on conditions

Hi All,

 

I am looking to reassign a value based on specific conditions, here is my table below...

 

NameAmountDelivery Day
A1Blank
A2Day 1
A1Day 1
A5Day 2
B4Day5
B5Blank
B12Day2
C7Day 3
C8Day 1
C17Day5

 

for example I would like the Delivery Day to be reassigned to be the Delivery Date that is over 50% of the total amount (solution can't be max) and here is what I would therefore expect to see...

 

NameAmountDelivery Date
A6Day 2
B15Day 2
C24Day 5

If this solution was a calculated column that would be great! 😊

 

Many Thanks in advance for your help!

 

Taylor

2 ACCEPTED SOLUTIONS

hi @Ttaylor9870 

try to plot a table visual with Name column and two measures like:

AmountSum = SUM(TableName[Amount])

MaxDeliveryDate = 
MINX(
    FILTER(
        TableName, 
        TableName[Amount]=MAX(TableName[Amount])
    ),
    TableName[Delivery Day]
)

 

it worked like:

FreemanZ_0-1676944076852.png

View solution in original post

Hi @FreemanZ,

 

How would this then look if you were to change it to No of rows opposed to Amount?

NameAmountDelivery Day
A1Blank
A2Day 1
A1Day 1
A5Day 2
B4Day5
B5Blank
B8Day 2
B12Day2
C7Day 3
C8Day 1
C17Day5
C1Day 1

 

CompanyAmountDelivery Day
A9Day 1
B29Day 2
C33Day 1

View solution in original post

8 REPLIES 8
FreemanZ
Super User
Super User

hi @Ttaylor9870 

can you describe the calculation behind?

Hi @FreemanZ ,

 

There was no calculated columns or measures to create the columns that's they way they naturally sit. 
I'd like the amounts added up and then whatever Delivery day had more than 50% of the total then assign delivery date to that.

hi @Ttaylor9870 

how do you come with 6, 15, 24?

Apologies! My bad I've edited the post and forgot to edit my expected table here is it fixed...

My Table...

 

NameAmountDelivery Day
A1Blank
A2Day 1
A1Day 1
A5Day 2
B4Day5
B5Blank
B12Day2
C7Day 3
C8Day 1
C17Day5

 

Expected result...

 

NameAmountDelivery Date
A9Day 2
B21Day 2
C32Day 5

 

hi @Ttaylor9870 

try to plot a table visual with Name column and two measures like:

AmountSum = SUM(TableName[Amount])

MaxDeliveryDate = 
MINX(
    FILTER(
        TableName, 
        TableName[Amount]=MAX(TableName[Amount])
    ),
    TableName[Delivery Day]
)

 

it worked like:

FreemanZ_0-1676944076852.png

Hi @FreemanZ,

 

How would this then look if you were to change it to No of rows opposed to Amount?

NameAmountDelivery Day
A1Blank
A2Day 1
A1Day 1
A5Day 2
B4Day5
B5Blank
B8Day 2
B12Day2
C7Day 3
C8Day 1
C17Day5
C1Day 1

 

CompanyAmountDelivery Day
A9Day 1
B29Day 2
C33Day 1

hi @Ttaylor9870 

what do you mean by No of rows opposed to Amount?

Hi @FreemanZ ,

 

No of Delivery Day rows, for example in the table above Compnay A had 2 Delivery Day 1 so make a new column that then assigns Company A to Delivery day 1.

Many Thanks for your help! 😊

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.