Calculate number of weeks in a range

[sportyarod]

I have a dataset where it has ship days for each railcar.  I created a table to show the number of railcars for each week and then what the lower and upper ranges are, as well as double the average.  I then calculated a column to determine if the range is either below, average, above, or double.  Where I am having trouble is creating a formula to determine how many weeks fall in the below average range.  I have tried a few different things but nothing seems to be coming up correctly.  It just automatically groups everything together and shows as double average.

[Anonymous · Accepted solution]

 Thanks for the reply from @Ashish_Mathur @lbendlin , please allow me to provide another insight:
Hi, @sportyarod 

Regarding the issue you raised, my solution is as follows:

1.First I have created the following table and the column names and data are the data you have given:

2. Below are the Calculated Columns I've created for your needs:

average range = SWITCH(TRUE(),
'railcar'[Count of Reilcar]<'railcar'[Satdev lower range ],"below average",
'railcar'[Count of Reilcar]>='railcar'[Satdev lower range ]&&'railcar'[Count of Reilcar]<'railcar'[Satdev upper range ],"average",
'railcar'[Count of Reilcar]<'railcar'[Double Average]&&'railcar'[Count of Reilcar]>='railcar'[Satdev upper range ],"above average",
'railcar'[Count of Reilcar]>='railcar'[Double Average],"double","worng")

3. Below are the measure I've created for your needs:

weeks = CALCULATE(COUNTROWS('railcar'),FILTER(ALLSELECTED('railcar'),'railcar'[average range]="below average"))

4.Here's my final result, which I hope meets your requirements.

Best Regards,

Leroy Lu

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

[Ashish_Mathur]

Hi,

Does this measure work?

Measure = filter(values(Calendar[Week_Ship]),[Average ratings]="Average")

[lbendlin]

You cannot measure a measure directly. Either materialize it first, or create a separate measure that implements the entire business logic.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...