Excel Magic Trick 1454 DAX Measure Count Customer Totals Between Upper Lower Limits, Each Month

Count Customer Totals Between Upper & Lower Limits For Each Month using a DAX Measure in either Power Pivot or Power BI Desktop. See the DAX Functions: COUNTROWS, FILTER, MAX and MIN and an AND Logical Test. Create a Cross Tabulated Frequency Distribution that shows a count of Customer Totals for each month and each Sales Category.
Download File:

Related Videos:
Excel Magic Trick 1453 Array Formula Count Customer Totals Between Upper Lower Limits, Each Month
Excel Magic Trick 1454 DAX Measure Count Customer Totals Between Upper Lower Limits, Each Month

[nattawut_chatwiriyacharoen]

Thanks a lot Mike
I love the way you demonstrated how formula works. It's crystal clear.
Before seeing your video, I think the formula should be

CALCULATE(
COUNTROWS(dCustomer),
FILTER(
dCustomer,
[Total &&
[Total
)
)


Your formula is more compact. After testing time consumed by DAX studio, there was no difference with these 2 formulas. However, I preferred yours. ^___^

[Nazeerul_Hazard]

You simply explained a complex topic of how the hidden calculate triggers the context transition. Grate explanation. Thanks a lot Mike!!!

[arekbombka]

Great explanation, especially the second part ... where you show / visualize how DAX works in memory behind the scenes based on a excel spreadsheet. I guess that is the hardest part for excel users starting to learn DAX, at least for me. By the way never saw this kind of explanation / visualisation on the web. AWESOME !!!

[Victor-ollo]

This video really helps to understand the how DAX is working behind the scenes. Thanks a lot Mike. It would be great to have more examples in future how the iterator functions like FILTER, SUMX, etc. work. As always: Thumbs Up and looking forward for more.

[himanshudalai]

Superb One !!! Thank you Mike for this great learning !!!

[maneshzaveri]

Thanks Mike ....very informational and useful

[DougHExcel]

thanks for the video, especially the picture tab showing what goes on in the background when DAX is going thru the calculations.

[rrrprogram]

Thanks for the great video mike ... I am thoroughly enjoying your video's..

[nishantkumar]

I love excel new data modeling feature and day by day it keeps adding new features which is awesome.

[mansouribrahim]

thank you , your videos help me so much .

[mirooovuujjj]

Thank you very much for sharing this video

[barttitulaerexcelbart]

Very good video Mike. Very well done with the second part with the sheet picture. Filter is a difficult formula.
3 remarks:
1: do you know how to suppress that warning at the right top corner. Excel wants this relation. It would be nice if you could assign a table as a disconnected table. The warning keeps coming...
2: In regular pivot, there is a group function, in power pivot it takes some effort to build your classes/buckets. I hope this will be a standard feature in new versions of Excel....

[starshiloh]

Hi does anyone know here how to sumif a cell that is empty? I have a sheet where certain cells are blank but when the cell is blank it always means it's 8. Can I do a sum to say sum the blank cells as 8 if blank? The formula I using now is a sumif. So I wonder if there is a formula I can combine with the sumif to count the blank cells as 8?

[johnborg]

Great Video Mike.However Formulas are fun, even though you said that they calculate slower :)

[Sal_A]

How is the MIN and MAX in your DAX formula reading the column criteria such as "$0 up to $1000." The MIN is returning $0 and the Max is returning $7000, but how does it read the column criteria such as "$0 up to $1000"? Because the "Total Sales Category" is a column of the disconnected table?

[technicalas]

I have some cells in column A on an Excel sheet and there is some text in it. I want to see that in the cell a particular cell which is written in the last cell ... vba

[udemaraful]

Please can you please explain how i can combine this 2 formulas in excel
Formula
Formula

This 2 formulas bring both a price

Please help

[HusseinKorish]

Mr.Mike ... i have a question: I'm an accountant and want to use Excel power query to build a simple Financial statement and i already have 12 text files (from Jan to Dec) exported from the accounting system represent the opening and movements and closing transaction with no header for columns, HOW can i make them (Append) like one table vertically so i can later use pivot table to build Financial Statement ?! thanks in advance