cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dpc_development Regular Visitor
Regular Visitor

Get calculated measure for a particular dimension only

I have a dataset with a field called 'cr'. It is basically the following iterative product formula...

Today's CR = Yesterday's CR * (1 + rate)

 

The requirement is to graph the 'cr' value, but to restart it from '1' in the visual, even after the filters are changed. So I have two calculated measures...

 

1. first_cr, which is basically the first value in the filtered time range

first_cr = 
VAR list = SELECTEDVALUE('CMC Daily Return'[fk])
VAR min_updated = CALCULATE(MIN('CMC Daily Return'[last_updated]), 'CMC Daily Return'[fk] = list, ALLSELECTED('CMC Daily Return'))
RETURN
CALCULATE(
    SELECTEDVALUE('CMC Daily Return'[cr]),
    'CMC Daily Return'[last_updated] = min_updated,
    ALLSELECTED('CMC Daily Return'[fk])
)

 

2. The cumulative return formula starting from 1

cumulative_return = IF([first_cr]>0, AVERAGE('CMC Daily Return'[cr])/[first_cr])

 

Firstly, is my first_cr formula the most efficient formula possible to get the first 'cr' value within a filtered time range of the dataset?

 

Secondly, I now need to find the 'cumulative return' of a particular dimension to use it as a benchmark in other visualisations or formula. I tried using the above calculated measures in various methods, but either there were no values or the visualisation did not respect the date filter due to some reason.

 

Eventually, I was able to get the following formula working, which is essentially recalculating everything. Can the above and below formulas be simplified further?

 

item1_return = 
VAR min_updated = CALCULATE(MIN('CMC Daily Return'[last_updated]), ALLSELECTED('CMC Daily Return'))
var onDate = MAX('CMC Daily Return'[last_updated])
var item1_first_cr = CALCULATE(
                    [first_cr],
                    'CMC Daily Return'[last_updated] = min_updated,
                    'CMC Daily Return'[fk] = "item1",
                    ALLSELECTED('CMC Daily Return')
                )
var item1_cr =     CALCULATE(
                    AVERAGE('CMC Daily Return'[cr]),
                    'CMC Daily Return'[last_updated] = onDate,
                    'CMC Daily Return'[fk] = "item1",
                    ALLSELECTED('CMC Daily Return')
                )
RETURN
IF(item1_first_cr > 0, item1_cr / item1_first_cr)

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Get calculated measure for a particular dimension only

Hi @dpc_development ,

 

The formula should get the same result as yours.

 

item1_return = 
var onDate = MAX('CMC Daily Return'[last_updated])
var item1_first_cr = CALCULATE(
                    [first_cr],
                    'CMC Daily Return'[fk] = "item1",
                    ALLSELECTED('CMC Daily Return')
                )
var item1_cr =     CALCULATE(
                    AVERAGE('CMC Daily Return'[cr]),
                    'CMC Daily Return'[last_updated] = onDate,
                    'CMC Daily Return'[fk] = "item1",
                    ALLSELECTED('CMC Daily Return')
                )
RETURN
IF(item1_first_cr > 0, item1_cr / item1_first_cr)

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
Community Support Team
Community Support Team

Re: Get calculated measure for a particular dimension only

Hi @dpc_development ,

 

The formula should get the same result as yours.

 

item1_return = 
var onDate = MAX('CMC Daily Return'[last_updated])
var item1_first_cr = CALCULATE(
                    [first_cr],
                    'CMC Daily Return'[fk] = "item1",
                    ALLSELECTED('CMC Daily Return')
                )
var item1_cr =     CALCULATE(
                    AVERAGE('CMC Daily Return'[cr]),
                    'CMC Daily Return'[last_updated] = onDate,
                    'CMC Daily Return'[fk] = "item1",
                    ALLSELECTED('CMC Daily Return')
                )
RETURN
IF(item1_first_cr > 0, item1_cr / item1_first_cr)

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
dpc_development Regular Visitor
Regular Visitor

Re: Get calculated measure for a particular dimension only

@v-frfei-msft I guess I didn't need the additional min_updated.

 

I'd also appreciate a comment on whether my first_cr formula or the method I am using to calculate a metric as percent of the first/earliest metric value in a date range is the best that it can be, or is a more efficient formula possible.

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

Top Ideas
Users Online
Currently online: 111 members 1,464 guests
Please welcome our newest community members: