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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

Frequent Visitor

## Divide with a dynamic denomitanor

Hi,

Here is an example of my table

 Years Dates Var 2018 2018-05-23 1 2018 2018-06-24 0 2018 2018-05-25 1 2019 2018-07-12 1 2019 2018-08-15 1 2023 2023-05-31 0 2023 2023-06-12 1

I'd like to calculate a ratio between the sum of Var and the total of days.

The denominator should be the sum of days for the 5 months (May, June, July, August, September) if the Year is <

YEAR(TODAY()) otherwise it should be the number of days from May 1 to Yesterday.
I've started by creating a new column :
YearToday = YEAR(TODAY()) (to catch the year , here it's 2023)
And using it in another New column :

``NbrOfDays = IF('Table'[Years]<'Table'[YearToday],DATEDIFF(DATE('Table'[Years],05,01),DATE('Table'[Years],09,30),DAY),DATEDIFF(DATE('Table'[Years],05,01),MAX('Table'[Dates]),DAY))``

It's work : I got 152 days for 2018, 2019 and 120 for 2013.

My problem is that I can't use this column to create my Mesure :  divide(sum(var), NbrOfDays)

Can you help me.

Thanks

1 ACCEPTED SOLUTION
Super User

@Nissa23 although this will work:

``divide(sum(var), MAX(NbrOfDays))``

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

8 REPLIES 8
Super User

@Nissa23 although this will work:

``divide(sum(var), MAX(NbrOfDays))``

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Frequent Visitor

@parry2k Thanks for your help. It's working.

Super User

@Nissa23 can you please provide the clear data? You are making it super complex for no reason. The var value in your original post is just 1 or 0's

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Super User

@Nissa23 let me ask you, what is your expected output based on the original data you shared?

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Frequent Visitor

Here is what should my result

 year Ratio(formula) Ratio(%) 2018 sum(var)/NbrOfDays =152 1,3 2019 sum(var)/NbrOfDays=152 1,3 2023 sum(var)/NbrOfDays=120 0,8
Super User

@Nissa23 if you are adding a measure, you cannot use a column, you have to aggregate it, either by SUM/MIN/MAX/AVG etc.

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Super User

@Nissa23 you have also aggregate nbrofdays if you adding a measure:

``divide(sum(var), sum(NbrOfDays))``

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Frequent Visitor

@parry2k The formula :

``divide(sum(var), sum(NbrOfDays))``

should be (NbrOfDays is already computed in the above formule):

``divide(sum(var), NbrOfDays)``

because NbrOfDays is a column, I'm not able to use it that's why I'm asing for help.

Thanks

## Helpful resources

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

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

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors