Counting rows in a visual (table) with one measure and two dimensions

[masch112]

Dear community,

I am using If-functions to check my data for unusual values (like negative revenue).
I would like to count rows in a table that displays such an if-function.

The if-function looks like this

Negative Net Sales Total=

IF([KPI_ Net Sales Total]<0;
[KPI_Net Sales Total])

and the corresponding KPI like this:

KPI_Net Sales Total =
CALCULATE(SUM(Totals_All[GC Wert invers]);
DATESYTD('Datum'[Datum]);
IncomeStatement[Ebene8] = "Net sales total")

The problem I am facing is that I use two additional dimensions in the table, company and account name.
In order to count the rows in this table, I somehow have to aggregate/group/summarize the measure by company and account name. If I don't aggregate the measure, no value is returned as the revenue IN TOTAL is not negative.



I've managed to achieve this in QlikView before, this is the expected output:

In QV, in order to count the rows, you would distinctcount the company & account name, by aggregating them with this expression:
Count(Distinct aggr(IF($(KPI_Net Sales Total)<0 , [Name Financial Statement Item] &[Company Name]), [Company Name], [Name Financial Statement Item])) 

Does anyone know how to achieve this? I think I have to use either groupby or summarize in DAX but I am not yet familiar enough with the syntax.

Thank you very much in advance!

Best regards,
Mats

[v-caliao-msft · Accepted solution]

@masch112,

In order to count the rows in this table, I somehow have to aggregate/group/summarize the measure by company and account name.

You could use ALLEXCEPT function to count the row group by Company. This function removes all context filters in the table except filters that have been applied to the specified columns. Smaple DAX for you reference.
Count = CALCULATE(COUNT(Table3[Value]),FILTER(ALLEXCEPT(Table3,Table3[Group]),Table3[Value]<0))

If this is not what you want, please elaborate your requirement, so that we can make further analysis.

Regards,

Charlie Liao