Forum Discussion

PDS001's avatar
PDS001
Regular Visitor
3 years ago
Solved

Reference different tables when creating a measure

I am trying to create a Measure within a table called 'All Sales'. The Measure should calculate the ratio of times "1" appears in the [Sales] column in the 'Sales Numbers' table, to the number of rec...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    3 years ago

    Hi,

    Create a single column table called Records with only unique Record ID in that table.  Create a relationship (Mnay to One and Single) from the RecordID column of the 2 data tables to the RecordID column of the new table.  Write these measures and drag the last one to a card visual

    Measure1 = calculate(COUNTROWS('sales'),'Sales'['Sales Numbers'], == "1")

    Measure2 = COUNTROWS('All Sales')

    measure3 = divide([Measure1],[Measure2])

    Hope this helps.

lbendlin's avatar
lbendlin
Super User
3 years ago

Read about CALCULATE()

PDS001's avatar
PDS001
Regular Visitor
3 years ago

OK I think with the above suggestions and correcting the syntax I've worked it out.

 

As Ahsish Mathur says you can create two seperate Measures, then divide those. This makes it easier to follow. Originally I wanted to avoid this to minimise processing time of 3 measures when loading in millions of row data situations, and creating more items in the Fields section.

 

So combining this into one equation reads:

 

Measure = DIVIDE(COUNTROWS('All Sales'), CALCULATE(COUNTROWS('Sales'),'Sales'[Sales Numbers] == "1"))
 
So the key point for others if I am correct:
COUNTROWS only counts the rows in a Table not a column, so you can only reference tables.
To find the quantity of specific values in a column, you filter the table using CALCULATE then count the rows remaining.
So:
CALCULATE(COUNTROWS('Sales'),'Sales'[Sales Numbers] == "1"
reads as:
Count the rows left in the Sales table after the filter Sales Numbers = 1. Where the term CALCULATE creates the filter , not COUNTROWS.
 
Interesting thought though. Would using the 3 measures method be just as quick to load as the 1 measure method?