Count based on measures using an IF measure

[Anonymous]

Hi,

I have a number of tables I have summarized (I've done with measures), then I evaluated the two measures with another measure, at which point I would like to get a count.  I can get them to list in a table visualization but, I can't get a count to work.

Tables:

1. Customer - [CustomerNumber]

2. CustomerAsset - [Available Volume]

3. Sales - [InvoiceNumber], [Volume]

Measures:

Volume = sum(Sales[volume])

Available Volume = sum(CustomerAsset[AvailableVolume])

Available Vol Adjusted = Available Volume *.95

Filters:

A measure to tell me -   Run Out = IF([Volume]>=[Available Vol Adjusted],"RunOut","NotRunOut")

This is what the table visualization will list for me with the 'Run Out' measure in the visual filter.  This is correct.  Now, I want to count the number of invoices in this list but, it will only give me a total of all of the invoices when I try to count.  I feel like I need to create a table with these columns and the runout measure but, my attempts to create a table are not working....I just can't wrap my head around it.  Looking for some experience and bigger brains than mine.  TIA!!

CustomerNumber	InvoiceNumber	Volume	Available Volume	Available Vol Adjusted
2959506	373037	618	646	613.7
2930706	368734	617.7	646	613.7
2835354	365475	855.1	855	812.25
2748731	346003	759.6	760	722
2570762	374297	845.2	855	812.25
11096864	394249	814.3	855	812.25
10560977	384517	645.6	646	613.7
10374726	390164	819	855	812.25
10360535	350080	853	856	813.2
10275998	365197	833.2	855	812.25
10260388	334705	813.8	855	812.25

[v-yuezhe-msft · Accepted solution]

Hi @Anonymous,

I am not sure what is the raw data in your tables (Customer, CustomerAsset, Sales). However, based on the above table visualization, we can create the measure using the following formula to calculate number of invoices which are “Runout”.

Measure = CALCULATE(COUNT(Table1[InvoiceNumber]),FILTER(Table1,[Volume]>=[Avaliable Vol Adjusted]))

Replace Table1 with your table name and check if it returns expected result. If not, please post sample data of your tables(Customer, CustomerAsset, Sales) and we also need to know the relationships between these tables in Power BI Desktop.

In addition, in your current table visualization, you don’t have to create a measure to calculate number of invoices, you can click the InvoiceNumber field in the first screenshot and select “Count(Distinct)”, you will get a total number of invoices in table visual as long as you turn off Total button as shown in the second screenshot.

Thanks,
Lydia Zhang

[Baskar]

Hi dude

can u please share yourcount formula which u used to count 

[Anonymous]

I just used a simple DISTINCTCOUNT (invoice number) to count my total number of invoices...I want to count the invoices in the table visualization (listed using measures above and filtering with a measure)...I need to summarize the volumes on invoice because I have more than one line per invoice and compare that to more than one line of assets on the customer...then determine the 'RunOut'...anytime I try to use a count (countx, countax) formula I get the error that the 'calculated column does not have data'...which makes sense because I'm not using a calculated column, I'm using measures...this makes me think I need to join the tables somehow and then create a calculated columns...I attempted this using SUMMARIZE( I'm a noob,my DAX skills are not great) but, it wasn't calculating my volumes right (it was totalling all volumes on each line)

In sql I think it would look something like this?
Customerassets INNERJOIN Sales on customer
INNERJOIN Customer on customer

Then, could I create a column with my volume measures, the run out measure (hopefully it won't add everything together) ...then use a countx() or something to filter on the 'Run Out'?

[v-yuezhe-msft · Accepted solution]

Hi @Anonymous,

I am not sure what is the raw data in your tables (Customer, CustomerAsset, Sales). However, based on the above table visualization, we can create the measure using the following formula to calculate number of invoices which are “Runout”.

Measure = CALCULATE(COUNT(Table1[InvoiceNumber]),FILTER(Table1,[Volume]>=[Avaliable Vol Adjusted]))

Replace Table1 with your table name and check if it returns expected result. If not, please post sample data of your tables(Customer, CustomerAsset, Sales) and we also need to know the relationships between these tables in Power BI Desktop.

In addition, in your current table visualization, you don’t have to create a measure to calculate number of invoices, you can click the InvoiceNumber field in the first screenshot and select “Count(Distinct)”, you will get a total number of invoices in table visual as long as you turn off Total button as shown in the second screenshot.

Thanks,
Lydia Zhang

[Anonymous · Accepted solution]

Hey Lydia!!

OHHHH snap!! I just got it to work!!  I created a calculated column in the Sales table using the measure [Available Volume Adj] aggregated from the customerasset table and then applied your formula and BOOM!!  I have a count of 11!!  DISTINCTCOUNT might be overkill with my relationships but, I'm feeling 'better safe than sorry' 🙂

I could not get that calculated column to work before!! ....too much eggnog maybe....'tis the season!!!

RunOut Count = CALCULATE(DISTINCTCOUNT('FAC Sales'[InvoiceNumber]),FILTER('FAC Sales',[Volume]>=[Available Vol Adjusted]))

[Volume] being a SUM measure of 'Sales'

[Available Vol Adj] being SUM measure of 'CustomerAsset' and then a calulated column in the 'Sales' table

I LOVE PowerBI and DAX!!!  Merry Christmas and Happy New Year!!

Thanks Everyone!