Count number of rows in a visual table

[ErikHswe]

Hello,

I have a visual table containing 5 columns where 4 of those are measures.

Here is what they look:

Result this month = sum('q_view_pbi_invoice'[InvoiceAmountCUR])

Result last month = CALCULATE('q_view_pbi_invoice'[Result this month]; PREVIOUSMONTH('q_view_pbi_calendar'[CalendarDate]) )

Difference = q_view_pbi_invoice[Result this month] - q_view_pbi_invoice[Result last month]

Is difference positiv or negative = IF([Difference] <= 0; 0;1) 

Now, I want to count the last measure and display in a KPI how many results are negative and how many are positive for the current period (determined in a slicer visual). But I have tried a thousand things and nothing seems to work. Any advice would be appreciated.

[GAW]

Have you tried the Calculate function?

It calculates based on the current filtered view and not on the data behind the scenes

Works along the lines of Calculate(Sum(table.field))

Put this in a measure and you may get somewhere. Worked for me in the past..

[v-jiascu-msft]

Hi @ErikHswe,

Can you please share a sample .pbix file? What's the column that isn't a measure? This column is the context of the other measures. And [diff kr invoice] is also a measure. I think the formula could be complicated.

Best Regards!

Dale

[ErikHswe]

I'm afraid I can't share the .pbix since it contains company information. But here is a screen shot anyway that shows the visual:

Sorry I had not been translating everything in my original post to english. I have edited the code block now so it should make more sense. The column that is not a measure (seen in the image) is "CompanyName".

[Anonymous]

Hi, 

perhaps try sumx which calculates an expression at each row of the table and then sums the results.

[Positive Count] = SUMX('Table', [Is difference positiv or negative])

[Negative Count] = SUMX('Table', ([Is difference positiv or negative] - 1)^2)

So sumx will calculate the measure [Is difference positiv or negative] at each row of the table and then sum the result.

In the negative count measure I take away 1 so that the positive results go to zero and the negative count to -1 (I then square to make it positive). Then it sums up the results which will be a count of negative differences.

Hopefully that helps,

Will

[ErikHswe]

I tried that, but it counts the difference for all rows in the table. Not just the filtered context in the visual. See the screenshot I posted in my answer to v-jiascu-msft.

[v-jiascu-msft]

Hi @ErikHswe,

The following formula could give you some idea. I know I need to make some changes in some way. Please feedback if it's convenient for you.

CouldBe =
SUMX (
    SUMMARIZE (
        q_view_pbi_invoice,
        'q_view_pbi_calendar'[CalendarDate].[Year],
        'q_view_pbi_calendar'[CalendarDate].[Month],
        'YourTable'[CompanyName],
        "difference", SUM ( q_view_pbi_invoice[InvoiceAmountCUR] )
            - SUMX (
                CALCULATETABLE (
                    SUMMARIZE (
                        q_view_pbi_invoice,
                        'q_view_pbi_calendar'[CalendarDate].[Year],
                        'q_view_pbi_calendar'[CalendarDate].[Month],
                        'YourTable'[CompanyName],
                        "lastmonthAmount", SUM ( q_view_pbi_invoice[InvoiceAmountCUR] )
                    ),
                    PREVIOUSMONTH ( 'q_view_pbi_calendar'[CalendarDate] )
                ),
                [lastmonthAmount]
            )
    ),
    [difference]
)

Best Regards!

Dale

[ErikHswe]

Impressive formula to say the least! I got some errors testing it though. Could you point me inte the right direction?

The error I got was:

The variation 'Month' for column 'CalendarDate' specified in the 'SUMMARIZE' function was not found in the input table.