Forum Discussion

New Member

9 years ago

Solved

range measure

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

MFelix
9 years ago
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

Vvelarde

Community Champion

9 years ago

hanaschmidt

Hi, please try this:

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

hanaschmidt

New Member

9 years ago

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
Super User
9 years ago
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
  New Member
  9 years ago
  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
  - MFelix
    Super User
    9 years ago
    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

Forum Discussion

range measure

Related content

Dax Measure for Range

countrows range measure

How to fix range measure calculation

Creating a new measure from date ranges

Whitelist IP ranges