cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

Quarterly Ratio

Hi,

 

I have a column that gives me number of days for the quarter, say 10th of Jun is 42nd day in a 92 days within the quarter ( I have different fiscal calendar). Now I want to have a ratio of 42/92 for each day. 

Say I have a filter date that filters 10th of June - so my ratio is 42/92. If I filter 11th of June my ratio is 43/92 etc.

 

Does anyone know how to do it?

 

Thanks,

Rafal

7 REPLIES 7
Highlighted
Post Partisan
Post Partisan

Re: Quarterly Ratio

These calculations let you apply different aggregates at different levels in your data. For example, you can sum revenue up to the month level, and then take an average of that total to display at the year level.

 

The result would look something like this:

 

The calculations included in the Aggregate category are:

Average within category
Variance within category
Max within category
Min within category
Filters and baselines
This category of calculations lets you calculate values for a specific category in a column, or compare values to a specific baseline. For example, the following quick measure calculates the SalesAmount for just Deluxe Class products:

 

A card is a good way to display these filtered measures:

 

The calculations included in this category of quick measures are:

Filtered measure
Difference from baseline
Percentage difference from baseline
Time intelligence
If you need a measure that shows period-to-date or period-over-period values, the time intelligence category of calculations lets you do this.

For example, you can calculate the sales amount over the order year to date.

Here’s the result of that calculation alongside the unaggregated value:

The included calculations are:

Year-to-date total
Quarter-to-date total
Month-to-date total
Year over year change
Quarter over quarter change
Month-over-month change
At present, the time intelligence calculations only work with date fields with Power BI’s built-in date hierarchies. If you’re using your date field in visuals and see Date Hierarchy on the field well menu, then you can use that field in the time intelligence quick measures.

We’re looking to remove that date hierarchy restriction in the future, so you can easily use your own date tables too.

Running total
This category (it’s just one calculation, so far) is similar to the YTD calculation, but lets you apply a running total over any field, not just dates.

Here’s an example adding up the sales amount over each product class:

Mathematical operations
These calculations run simple mathematical functions, which can become powerful when chained together.

Here’s an example showing a few chained together to produce line item total values.

The included calculations are:

Addition
Subtraction
Multiplication
Division
Percent difference
You can create a quick measure by selecting Quick measures from the field menu, either in the field well of a chart or from the field list.

In the dialog, you can choose which of the 19 different calculations you want to perform on your selected measure. Depending on the calculation, you’ll see different parameters you’ll be required to fill in. You can also change which fields are being used in the calculation by using the field list on the right half of the dialog.

After you provide the required parameters and click OK, the quick measure is now part of your model and available to use in your other visuals. If you had originally launched the quick measures dialog from the field well of a visual, the new measure is added to the visual.

In addition, just like any other measure, you can see the DAX formula that Power BI used to create this new measure. This is a great way to learn more about DAX.

 

Admin

Apps4rent | o365cloudexperts

Highlighted
Helper II
Helper II

Re: Quarterly Ratio

hi, this is not what I meant - I am aware of this time intelligence functions.

I just need a dynamic ratio, quarterly ratio displayed as %...

Highlighted
Community Support
Community Support

Re: Quarterly Ratio

Hi @Czempijan ,

 

You may create measure like DAX below.

 

Quarterly Ratio=

Var _DayRank=CALCULATE(COUNT(Table1[Date]),FILTER(ALLSELECTED(Table1), YEAR(Table1[Date] )=YEAR(MAX(Table1[Date]))&&MONTH(Table1[Date] )=MONTH(MAX(Table1[Date]))&&Table1[Date] <=MAX(Table1[Date])))

Return

_DayRank / [number of days for the quarter column]

 

Best Regards,

Amy 

 

Community Support Team _ Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Highlighted
Helper II
Helper II

Re: Quarterly Ratio

thanks, I did not understand the last part :

_DayRank / [number of days for the quarter column]

 

so by what I shall divide?

 

Thanks,

 

Highlighted
Community Support
Community Support

Re: Quarterly Ratio

Hi @Czempijan ,

 

>>I have a column that gives me number of days for the quarter, say 10th of Jun is 42nd day in a 92 days within the quarter ( I have different fiscal calendar). 

 

It is referred to the "column that number of days for the quarter" in your words above, and it is 92 in your scenario currently, right?

 

Best Regards,

Amy 

 

Community Support Team _ Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Highlighted
Helper II
Helper II

Re: Quarterly Ratio

hi, yes but it is a column I need this as a measure not as a column is it possible?

Highlighted
Community Support
Community Support

Re: Quarterly Ratio

Hi @Czempijan ,

 

If you need a calculated column, then you may create columns like DAX below.

 

YearMonth= YEAR(Table1[Date] )*100+ MONTH(Table1[Date] )



Quarterly Ratio=

Var _DayRank=CALCULATE(COUNT(Table1[Date]),FILTER(ALLSELECTED(Table1), Table1[YearMonth] )= EARLIER(Table1[YearMonth]) &&Table1[Date] <=EARLIER(Table1[Date])))

Return

_DayRank / [number of days for the quarter column]

 

Best Regards,

Amy 

 

Community Support Team _ Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Ignite

Microsoft Ignite

This will be a conference that you do not want to miss!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Don't miss the Power BI Dev Camp this week!

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors