cancel
Showing results 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

Anonymous
Not applicable

## CALCULATE DIFFERENCE (IN %) BETWEEN FIXED NUMBER AND THE LATEST NUMBER SELECTED

Hi!

This graph shows the number of calls and non-calls per region, and I need to create a measure to calculate the difference (in percentage) between the CW 11 – our reference - and the latest column selected by the user.

In this case, for example: CW 27 divided by CW11.

Obs: CW 27 can be any other week, and CW11 is fixed.

I can’t do that because I don’t know how to “ignore” on calculation the numbers between the columns and how to consider the latest CW selected.

*Caledar week: CW

Do you have any idea, please?

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi, @v-jayw-msft ,

I tried to add it, but still didn't work.

Anyway I found another solution:

Minimum value = CALCULATE(MIN('Traffic Development'[Total of Contacts]), FILTER('Traffic Development', 'Traffic Development'[Index] = MIN('Traffic Development'[Index])))
Maximum value = CALCULATE(MAX('Traffic Development'[Total of Contacts]), FILTER('Traffic Development', 'Traffic Development'[Index] = MAX('Traffic Development'[Index])))
Difference between last and first value = [Maximum value]/[Minimum value]-1
An finally it worked out. 🙂
4 REPLIES 4
Super User

@Anonymous , in case you have date and date table

measure =

var _min = minx(allselected(Date),Date[Date])
var _max = maxx(allselected(Date),Date[Date])

return
calculate( [measure], filter(all(Date), Date[Date]=_max)) - calculate( [measure], filter(all(Date), Date[Date]=_min ))

if you have week and week table, else have week table

measure =

var _min = minx(allselected(week),week[week])
var _max = maxx(allselected(week),week[week])

return
calculate( [measure], filter(all(Date), Date[Date]=_max)) - calculate( [measure], filter(all(Date), Date[Date]=_min ))

Anonymous
Not applicable

Hi, Mr. @amitchandak.

I have a table with weekly data, so I need to consider this one:

measure =

var _min = minx(allselected(week),week[week])
var _max = maxx(allselected(week),week[week])

return
calculate( [measure], filter(all(Date), Date[Date]=_max)) - calculate( [measure], filter(all(Date), Date[Date]=_min ))

But I still have some doubts...

I'm trying to replace by my columns (name of table is "Traffic Development"), but it's not working. What is wrong?

I put in red above the part of measure that i didn't understand.

var _min = MINX(ALLSELECTED('Traffic Development','Traffic Development'[Calendar Week])
var _max = MAXX(ALLSELECTED('Traffic Development','Traffic Development'[Calendar Week])

Thanks again!

Community Support

Hi @Anonymous ,

The logic is to calculate the values for earliest week and latest week in your visual.

I noticed that in your formula you lost ")" in ALLSELECTED() function.

Should be:
MINX(ALLSELECTED('Traffic Development'),'Traffic Development'[Calendar Week])

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.
Anonymous
Not applicable

Hi, @v-jayw-msft ,

I tried to add it, but still didn't work.

Anyway I found another solution:

Minimum value = CALCULATE(MIN('Traffic Development'[Total of Contacts]), FILTER('Traffic Development', 'Traffic Development'[Index] = MIN('Traffic Development'[Index])))
Maximum value = CALCULATE(MAX('Traffic Development'[Total of Contacts]), FILTER('Traffic Development', 'Traffic Development'[Index] = MAX('Traffic Development'[Index])))
Difference between last and first value = [Maximum value]/[Minimum value]-1
An finally it worked out. 🙂

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

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

#### 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.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors