count rows but total must show max count

[Anonymous]

I need to show in a table object the total calls of a callcenter per day. (dax counta in the fact table)

But the total column on the end of the object must show the max value from any day, not the total count.

Example: 10, 20 and 15 calls in 3 days. Total column must show 20 (max value from any day) and not 45 (sum).

I am trying a lot of DAX, using summarize etc...

So far, did not get what I need.

[zlokesh]

Hi @Anonymous,

                 Please go through the following example..

The following example returns the largest value found in the ExtendedAmount column of the InternetSales table.

=MAX(InternetSales[ExtendedAmount])

Here InternetSales is table and ExtendedAmount is column

Thanks 

[Anonymous]

@zlokesh, I don´t want to see max value in a column. I want to see the max COUNTROWS from a table, and I have 7 filters on top of that. my previous posts show my progression on this issue, but I think you didn´t read all of it.

[zlokesh]

Hi @Anonymous,

          Ok you mean if you have 20(max value) 4 times (say example) then it should show the count 4, Right?

Thanks

[Anonymous]

I replied this but in another message.
https://community.powerbi.com/t5/Desktop/count-rows-but-total-must-show-max-count/m-p/477741#M222275

I count rows. If I have 4, 5 and 6 counts, I need to show 6 (max of count) and not 15 (total count).

I am not checking max values. I am checking max countrows. It doesn´t matter the values.

[Anonymous]

Thanks a lot for your effort.
https://www.dropbox.com/s/j5opr91tp87svme/max%20value%20from%20countrows.pbix?dl=0

In this link is my sample.

There are 3 levels of drilldown in the matrix object.

There are 4 filters on the page.

I need to show only one measure that counts rows in the matrix row.

The first measure is what is counting correctly, but for the total column at the far right I need to show 47 and not 181 (first level drilldown, no filters).

The second measure only works for "week" level of drilldown because of the allexcept condition.

If I put the other 2 columns from drilldown in the allexcept, then no countrows is incorrect for any level. All wrong.

If I put the 4 filters in the allexcept, same problem.

If I remove allexcept, I get all wrong values.

If I create 3 measures, one for each level of drilldown, then the isfiltered command does not show totals.

I really apreciate any help.

[v-jiascu-msft]

Hi @Anonymous,

The trick here is context. To make it simple, I would suggest you try the formula below. I don't know if there are any other columns, that's why I used ALLEXCEPT.

Measure 11 =
MAXX (
    Table3,
    CALCULATE ( SUM ( Table3[Value] ), ALLEXCEPT ( table3, Table3[Date] ) )
)

Best Regards,

Dale

[Anonymous]

Hum, ok. I got the logic, the idea, but it is not working.

instead of SUM, I need to use COUNTROWS.
my original measure uses filter too...
CALCULATE(COUNTROWS(table3); FILTER(table3;table3[value] > 0))

this measure gives correct values for each date on the table object, but total column must be max and not total row count.

[Anonymous]

it worked!

this is my measure. I know it is ugly, but it works.

I am using 2 calculates, 2 filters... maybe you can help me optimize it.

MAXX (
table;
CALCULATE ( CALCULATE(COUNTROWS(table);
FILTER(table;table[value1] > 0)); ALLEXCEPT( table;table[value2]) )

[Anonymous]

hum... new problem.
I use this measure in a table object, with 3 levels of drilldown: week, date and hour.
when I put this 3 columns in the allexcept, values does not work and is the same value for all 3 levels of drilldown.

it seems that value is from only last week, last day, last hour. i really don´t know, but I know values are wrong for all 3 levels of drilldown.

[Anonymous]

I have 3 levels of drilldown. and 4 other filters on the same page.
I managed to make 3 measures, one for each level of drilldown. but the allexcept makes the other 4 filters on the page not work.

below, I just change date to week or hour between the 3 measures.

MAXX (
table1;
CALCULATE(COUNTROWS(table1);
FILTER(ALLEXCEPT( table1;table1[date]);
table1[value] > 0)))

if I remove the allexcept and put only the plain table1, the total gets full countrows and not only the maxx value.

[v-jiascu-msft]

Hi @Anonymous,

It would be great to have a sample.  Can you provide one please?

Best Regards,

Dale