Excel Magic Trick 1387: Data Model & DAX Formulas: Quantity Frequency Distribution by Year & Country

Download Files:

See how to Create a Data Model with DAX Formula to build PivotTable Frequency Distribution with Conditional Formatting. Also learn how the Data Model and DAX Formulas work on Big Data:
1. (00:13) Introduction
2. (03:42) Build CountCategory Table in Excel. Learn about Table Formula Nomenclature in Excel Table.
3. (07:50) Build DAX Measure in Measure Grid to Calculate MAX Quantity
4. (09:36) Import CountCategory Table into Data Model
5. (10:05) Create Automatic dCalendar Table in Power Pivot
6. (11:14) Build Relationships Between Tables. Discussion “Filter Context” and of how Criteria from PivotTable filters Lookup Tables and how the “filter” flows across the Relationship to “filter the Fact table down to a smaller size, which helps DAX formulas to calculate quickly on Big Data.
7. (13:41) Build DAX Calculated Column in Fact Table to use in Relationship to CountCategory Table. Discussion of Columnar Database and how columns with a small number of unique values do not take up much RAM memory. See the CEILING DAX Function.
8. (18:01) Build PivotTable based on Data Model
9. (18:22) Sort Count Category Column by Upper Value Column so categories sort correctly in PivotTable
10. (20:04) Build DAX Measure Frequency Formula to count # transactions with specified quantity. Description of How Data Model and DAX Formulas calculate when criteria is applied in a PivotTable. See the COUNTROWS function.
11. (23:11) Finish Building PivotTable.
12. (23:51) Add Slicer to PivotTable
13. (24:58) Add Conditional Formatting to PivotTable
14. (26:03) Add Formatting to Dashboard
15. (27:48) Summary

[vida]

Mike, you should get an award for passing your Excel passion to others. Thank you very much.

[johnabram]

I never walk away from your videos without learning something. Besides the main topic, great as it is, I learned that you can double-click the format button to lock it. Somehow, I never knew something as trivial as that. The point being, you could have easily left that trivial thing out of the narrative of the main topic but a great teacher as you are, always keen to teach.

[LeilaGharani]

Mike! Your explanation is just great! So much gold in this video! Excel is indeed a lot of fun. I love the fact that you chose a big data set to create such a neat report. So many people are still under the impression that Excel is not meant for large data sets. Your tutorial goes hands-on with actual examples. Thank you!

[Pierre]

This series has absolutely changed the way I work. First class stuff!

[amadorcamacho]

Sir, as always, thanks, thanks. I hope that you're getting better and come back strong than ever.

[Victor-ollo]

Hi Mike! Great video that helps to understand the potential and power of DAX, especially if we are talking about really big data sets! Thumbs up !!!

[barttitulaerexcelbart]

Hello Mike, thanks again for this mighty video!

Actually I think grouping numbers should be a normal function in Power Pivot/Power BI. But for the moment we can work with your workaround or the more difficult one as described in Rob Colie's book.
One question: since you always prefer a short solution: why not use:

- convert to range (at the begining a list is never formatted as a table anyway....:) )
- Enter the formula's with traditional refferences.
- then convert this list to an Excel Table (Insert Table)
- This works : you can change the number 12 and you can also enlarge the table by copying the last line.

Looking forward to your next video Mike !

[NoShadowOfDoubt]

I watch DAX for fun now. thank you Mike!

[himanshudalai]

Thank you Mike for this awesome video. Lots of learning !!

[shoaibrehman]

Hi Sir,
If Count categories table Upper & Lower Columns are in percentages, & we need to check frequencies against fTable percentage column.
what kind of changes will required

[dmbers]

Hey, before I ask my question I would like to say thank you for your videos they've been a huge help.
Question. I am calculating numbers x-bar; double bar, r-bar, along with some other numbers. If I need to graph this How do I use one cell reference be the line in a graph, without having to make an entire column dedicated to that number (e.g. average, average range, UCL/LCL)

[johnmatta]

Hey Mike, why did you have to create a measure to count the rows..isn't possible if we just dragged any column that has text into the value aection to count...Thanks

[mohamedchakroun]

Thank you mike it is very nice....
I couldn't practise the exerices because when i put the formulas the file stop working immediately and excel restart, is there any alternative to build the upperlimitforrelationship?
Thanks another time