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

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

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
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.