Measure with Median value for each quarter

[chinthada]

Hello,

I want to display a measure with a Median value for each quarter (fiscal quarter). I created a dynamic calendar and linked it with the Data table. Data is like the below table. I need to display a visual chart with the median value of "fiscal year 2019 Q1"=XX, "fiscal year 2020 Q3"=xx etc.

thanks

[v-jayw-msft · Accepted solution]

Hi @chinthada ,

Please try the measure below.

Measure = CALCULATE(MEDIAN('Table'[value]),ALLEXCEPT('Table','Table'[fiscal year],'Table'[fiscal qtr]))

Best Regards,

Jay

Community Support Team _ Jay Wang

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

[chinthada]

Hello @v-jayw-msft ,

You made my day :). Thanks, it is working well.

regards,

[Greg_Deckler]

Tough to be exact as I don't believe I have all of the information I need. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

That being said, I am guessing something like:

Median = 
  VAR __Median = MEDIANX(RELATEDTABLE('Sales'),[Sales])
  VAR __Date = MAX([Date])
  VAR __Year = YEAR(__Date)
  VAR __Quarter = QUARTER(__Date)
RETURN
  "fiscal year " & __Year " & "Q" & __Quarter & "=" & __Median

[Tad17]

Hey @chinthada 

Check out this link as I think it answers your exact problem: https://community.powerbi.com/t5/Desktop/Calculate-Quarterly-Average-of-measure/td-p/252829

If this helps please kudo. 

If this solves your problem please accept it as a solution.

[chinthada]

Hi @Tad17 & @Greg_Deckler ,

Thanks for the reply. I think I should create a calculated column with the median value for each quarter. Can you help on that?

thanks

[Greg_Deckler]

Sure, kind of depends though. The formula provided was kind of intended for a column. Kind of depends on the relationship between the tables. You have a date table and then a sales table that has what kind of data in it? Dates? Quarters? Years? Months?

[chinthada]

Hi @Greg_Deckler 

Thanks for the reply. What I need to show is like below,

I have a source table like this,

To create the Quarterly median graph as above, I am thinking to create a calculated column (Median) like below,

What would you suggest?

thanks