Calculate and Count

[Capt.Ragnar]

I need to count both the total number of locations with the same "Facility Type" in the same "Year". Then I need to count the number of locations with greater cost metrics than each respective location within the same "Facility Type" and "Year".

Sample below:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Location[/TD]
[TD]Facility Type[/TD]
[TD]Year[/TD]
[TD]Cost Metric Value[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]A[/TD]
[TD]2013[/TD]
[TD]15.1[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]A[/TD]
[TD]2013[/TD]
[TD]16.2[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]B[/TD]
[TD]2013[/TD]
[TD]22.1[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]C[/TD]
[TD]2013[/TD]
[TD]35.0[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]A[/TD]
[TD]2014[/TD]
[TD]15.7[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]A[/TD]
[TD]2014[/TD]
[TD]17.0[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]B[/TD]
[TD]2014[/TD]
[TD]19.8[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]C[/TD]
[TD]2014[/TD]
[TD]36.2[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]C[/TD]
[TD]2014[/TD]
[TD]37.3[/TD]
[/TR]
</tbody>[/TABLE]

 

[scottsen]

Looks like you double posted, you might want to nuke the other one.

Let's look at the first problem, first. Do you know if you are hoping for a calculated column vs a measure / calculated field?

Assuming a column, it would be something like this:
=CALCULATE(COUNT(MyTable[Location]), FILTER(ALL(MyTable), MyTable[Facility Type] = EARLIER(MyTable[Facility Type]) && MyTable[Year] = EARLIER(MyTable[Year])))

For the 2nd half, I would look up the RANKX function and see if that will fit your needs?

 

[Capt.Ragnar]

I did indeed. This works. I'm still trying to learn the DAX vernacular...


Can you elaborate on what the function "EARLIER" does?

 

[scottsen]

Can you elaborate on what the function "EARLIER" does?

See if this helps: Writing a Subtotal Calc Column, AKA The Simplest Use of the Earlier() Function « PowerPivotPro

 

[Capt.Ragnar]

Perfect. Thanks.

I'm taking Rob's Class in Cleveland in July. I expect I'll realize then how uninformed that question was...

 

[Tianbas]

your second topic you could solve with the same formula that scottsen has posted. Just add
&& MyTable[Cost Metric Value]>earlier(MyTable[Cost Metric Value])