Forum Discussion
Count based on measures using an IF measure
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 |
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 ZhangHey 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!
4 Replies
Hi dude
can u please share yourcount formula which u used to count
- 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'?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
