Creating a measure to count the non-blank cells in a matrix

Question

I have a data table of how many passengers get on and off a bus. It records which section they got on and which one they got off. My table looks like this:

SectionIn  SectionOut  Pax
--------------------------
1          1           1
1          1           2
1          3           3
1          1           1
1          3           5
...etc...

With this data, I created a matrix with SectionIn on rows, SectionOut on columns, and the sum of Pax as the value.

First (and most important) I need to count how many instances you have in the matrix. That is, how many cells in the matrix have a value. In this case, I have 15 instances (1+2+3+4+5 values in the upper triangle). In Excel, it is easy with the COUNT formula (check the file please), but not dynamic.

Then, I need to calculate % of the grand total with filters and no filters (I think we have to considerate the evaluation context here). This are less important because they depend on the magic counting measure above.

Here you can find a sample database with a pivot table and the target I'm looking for. I created the target with Excel formulas but I need that in DAX so I can dynamically filter the pivot table and get the result in another pivot table.

Sample test: https://drive.google.com/file/d/1xtNdM7g8lWHvqF8UX4K3TP0uOp-fFIZ5/view?usp=sharing

I found easier using examples in that file than explaining with words what I need (hopefully you too), but if you don't understand please don't hesitate asking anything.

Thank very much in advance!

Regards,

Andy

It's not clear from the text what your question is. Sharing a file is very helpful but what you're asking needs to clear without downloading a file. — Alexis Olson
Hi Alexis! Thank you for replying, but it is a bit difficult to write down what I need. I mean, I don't want to write a book here and then you will use the file because it is too long (where I think it is clear). Please, give it a try! If it is a policy of the site I can describe everthing. — Andres Salomon
It's reasonably clear from your examples, but site policy states "DO NOT post images of code, data, error messages, etc.... Please reserve the use of images for diagrams or demonstrating rendering bugs, things that are impossible to describe accurately via text." If you can turn one of your examples into a minimal workable example, I'd be happy to answer. — Alexis Olson
Thank you for the clarification Alexis! I'm new into this kind of posts. I will modified the text. — Andres Salomon
Hi Alexis, please check it now if it is more clear the description. I used an image so you can see the structure of the matrix where I need to perform the calculations. There are more examples in the file. Thank you very much! — Andres Salomon

Alexis Olson Alexis Olson · Accepted Answer · 2019-12-12T15:44:32

You can create a summary table that lists each combination of SectionIn and SectionOut and then count the number of rows in that table. It would be just like your pivot table but with both on rows and look like this:

Here is the DAX code to do that:

CellCount =
COUNTROWS (
    SUMMARIZE (
        ALLSELECTED ( InOut ),
        InOut[SectionIn],
        InOut[SectionOut],
        "Passengers", SUM ( InOut[Pax] )
    )
)

(Note: You don't even need the "Passengers", SUM ( InOut[Pax] ) line for the measure to work.)

This is indeed dynamic as demonstrated here:

Creating a measure to count the non-blank cells in a matrix

1 Answers