range measure

[hanaschmidt]

hi All. Any advise how my formula for measure should look like. I am trying to calculate the number of contracts in range of the values between 500k and 1m. I have used the below formula to calculate number of contracts over 1m and worked: 

CALCULATE(COUNTROWS(Adults),Adults[Annual value]<1000000)

not sure how to do it for the range

thanks!

[Vvelarde]

@hanaschmidt

Hi, please try this:

MeasureName =
COUNTROWS (
    FILTER (
        Adults,
        Adults[Annual value] >= 500000
            && Adults[Annual value] <= 1000000
    )
)

[hanaschmidt]

@Vvelarde

thanks!!

I  have tried but received only umber 9 in column - like 9's in each row... doesnt give  an error but doesnt count properly 😞

[MFelix]

Hi @hanaschmidt,

Did try count or if the contracts are repetead distinctcount,something like this:

CALCULATE(COUNT(adults[contract number]),Adults[Annual value] > 50000 &&Adults[Annual value]<1000000)

Dont knoe if the contract number is called like that but you can select any column on your table to.be counted even the annual value.

Regards,

MFelix

[hanaschmidt]

@MFelix

Thanks but still cannot work it out 😞

I have got data exported which calls Sheet1. The spreadhseeet contains 200 contracts with different values. I have to create cards which will count contracts a) annual value all over 1m , b) annual value with the value 500k-1m and c) 170k-500k. I have been advised to create measure in new column for annual value in modeling then in report bit i can create cards for new columns. I have used the below formula for a) and it worked, it gave me values'1' in rows which applied.

over 1m = CALCULATE(COUNTROWS(Sheet1),Sheet1[Annual value]>1000000)

But cannot work out the formula for b) and c) 😞

Thanks for any advise

[Vvelarde]

@hanaschmidt

Do you a measure? Don't use a calculated column.

Look using a card with measure:

[hanaschmidt]

@MFelix it worked!!! 🙂 

Thank you so much to both of you!

@Vvelarde@MFelix

[v-ljerr-msft]

Hi @hanaschmidt,

Great to hear the problem got resolved! Could you accept helpful replies as solution as MFelix mentioned above to help others who may also have the similar issue easily find the answer and close this thread? 

Regards

[MFelix]

Don't forget to mark one of the replies as answer to the question.

[MFelix · Accepted solution]

Hi @hanaschmidt,

Just made a sample of your file with contracts number from 1-200 and random values. I have made the calculation in Excel and match it up with the Power BI. You can do this in at least 3 ways, as you can see below in all of them the result is the same, and I also validate this with Excel althought it's not on the print.

Measures:

Create 4 measure one for each of your value to use in each card:

MEASURE Above 1M = CALCULATE(COUNT(Contracts[Contract]);Contracts[Annual Value]>1000000)

MEASURE between 1M - 500K = CALCULATE(COUNT(Contracts[Contract]);Contracts[Annual Value]<=1000000 && Contracts[Annual Value]>500000)

MEASURE between 500K - 170K = CALCULATE(COUNT(Contracts[Contract]);Contracts[Annual Value]<=500000 && Contracts[Annual Value] >170000)

MEASURE Lower 170K = CALCULATE(COUNT(Contracts[Contract]);Contracts[Annual Value]<=170000)

 Then use this 4 measures to fill in the cards with the calculations you need.

Columns:

With columns you can do it in one of two ways:

First way  create one column for each range and place value 1 or zero and sum them on your card formulas below:

COLUMN ABOVE 1M = IF(Contracts[Annual Value]>1000000;1;0)

COLUMN between 1M - 500K = IF(Contracts[Annual Value]<=1000000 && Contracts[Annual Value]>500000 ;1;0)

COLUMN between 500K - 170K = IF(Contracts[Annual Value]<=500000 && Contracts[Annual Value]>170000 ;1;0)

COLUMN lower 170K = IF(Contracts[Annual Value]<=170000;1;0)

Formulas exactly as the measures only difference is that you have one IF to do the value 1 or 0

Second way create only one column with the types you need and then count those types on the card using the filters options

COLUMN WITH TYPE = SWITCH(
			TRUE();
			Contracts[Annual Value]>1000000;"ABOVE 1M";
			Contracts[Annual Value]<=1000000 && Contracts[Annual Value]>500000;"BEtween 1M-500K";
			Contracts[Annual Value]<=500000 && Contracts[Annual Value]>170000;"between 500K - 170 K";
			Contracts[Annual Value]<=170000;"Below 170K";
			"NOT DEFINED")

In this formula I use Switch because it's easier to read and to do than to concatenate several IF's one inside the other, then just apply the filter on the card:

Add the  colum COLUMN WITH TYPE to the Visual filter in order to use it.

Filters:

This is the just counting one of the columns in you table and adding filters based on the value of the contracts:

As you can see I'm counting the number of Annual Value and then filtering on the Annual Value you have to add the column Annual Value to the Visuals Filter to apply the filters.

You can also some other tricks like summary tables, editing queries with calculation, however the 3 above allows you to have different visuals in the same report in order to make them all interact with each other.

Hope this helps you, In the case of the measures  you can also replace the count by sum, distinct count, average, whatever you want to calculate an use it.

Regards,

MFelix

[SagayaMuthu]

Thanks, it solved one of querry to handle ranges within a column!!