Counting on row level unique values in MatrixTable

[Joop_de_Bont]

Hi,

I'am new to PowerBI and like to see if what I want is possible.
I have a matrix table with two row levels (Account Manager, Customer) and the count of Unique customer per month. Now I would like to count on row level in a column the total of how many month a customer has placed order(s).

On Account Manager level it should also be 12, but on customer level it could less as see with the last column

The question is how do it get this in PowerBI Desktop/Services as show below, any suggestion?

[Anonymous · Accepted solution]

Hi,audreygerred ,thanks for your concern about this issue.

Your answer is excellent!
And I would like to share some additional solutions below.
Hello,@Joop_de_Bont .I am glad to help you.
First of all, you need to be clear that the value of the Total column in the matrix in Power BI can not be directly modified, it itself as a non-existent data, the system is based on the logic you put into the matrix to perform the calculation of the measurement of simple aggregation operations, if the calculation of the logic of the measurement of SUM, then the total perform the same SUM, if the calculation of the logic of the measurement of DISTINCTCOUNT, then the total of the same DISTINCTCOUNT. If the measure's logic is DISTINCTCOUNT, then total performs the same DISTINCTCOUNT, and it is important to note that the logic of a complex measure is not always passed correctly to the total column, so the value of the total column may be incorrect.
The influence on the value of the total column can be indirectly implemented in the matrix by means of ISINCPOE.
You can get the logic of calculating Total in Power BI's matrix from the following code

Therefore, the total column is determined not to be a value in the row field and column field filtering hierarchy and can be set to a special value (99)

here is my test data.

here is my test measure:

M_Test = 
VAR _table =
 SUMMARIZECOLUMNS ( 'TestData'[CustomerName], 'MonthTable'[MonthName] )
RETURN
    IF (
        NOT ( ISINSCOPE ( 'TestData'[CustomerName] ) )
            && NOT ( ISINSCOPE ( 'MonthTable'[MonthName] ) ),
        [Total Month],
        COUNTX ( _table,[M_DisCount]  )
    )

Total Month = 
VAR _accountManger =
    MAX ( 'TestData'[AccountManager] )
VAR _discountPerYear =
    CALCULATE (
        DISTINCTCOUNT ( 'TestData'[MonthName] ),
        FILTER ( ALL ( 'TestData' ), 'TestData'[AccountManager] = _accountManger )
    )
RETURN
    _discountPerYear

M_DisCount = DISTINCTCOUNT('TestData'[CustomerName])

URL:

Solved: help COUNTX - Microsoft Fabric Community

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous · Accepted solution]

Hi,audreygerred ,thanks for your concern about this issue.

Your answer is excellent!
And I would like to share some additional solutions below.
Hello,@Joop_de_Bont .I am glad to help you.
First of all, you need to be clear that the value of the Total column in the matrix in Power BI can not be directly modified, it itself as a non-existent data, the system is based on the logic you put into the matrix to perform the calculation of the measurement of simple aggregation operations, if the calculation of the logic of the measurement of SUM, then the total perform the same SUM, if the calculation of the logic of the measurement of DISTINCTCOUNT, then the total of the same DISTINCTCOUNT. If the measure's logic is DISTINCTCOUNT, then total performs the same DISTINCTCOUNT, and it is important to note that the logic of a complex measure is not always passed correctly to the total column, so the value of the total column may be incorrect.
The influence on the value of the total column can be indirectly implemented in the matrix by means of ISINCPOE.
You can get the logic of calculating Total in Power BI's matrix from the following code

Therefore, the total column is determined not to be a value in the row field and column field filtering hierarchy and can be set to a special value (99)

here is my test data.

here is my test measure:

M_Test = 
VAR _table =
 SUMMARIZECOLUMNS ( 'TestData'[CustomerName], 'MonthTable'[MonthName] )
RETURN
    IF (
        NOT ( ISINSCOPE ( 'TestData'[CustomerName] ) )
            && NOT ( ISINSCOPE ( 'MonthTable'[MonthName] ) ),
        [Total Month],
        COUNTX ( _table,[M_DisCount]  )
    )

Total Month = 
VAR _accountManger =
    MAX ( 'TestData'[AccountManager] )
VAR _discountPerYear =
    CALCULATE (
        DISTINCTCOUNT ( 'TestData'[MonthName] ),
        FILTER ( ALL ( 'TestData' ), 'TestData'[AccountManager] = _accountManger )
    )
RETURN
    _discountPerYear

M_DisCount = DISTINCTCOUNT('TestData'[CustomerName])

URL:

Solved: help COUNTX - Microsoft Fabric Community

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Joop_de_Bont]

V-tjian,

Thanks for your give option, I will investigate and see if this helps me fixing the problem

joop

[Joop_de_Bont · Accepted solution]

Thnx, It looks like what i needed.

[audreygerred]

Hi! You can create 14 measures to achieve this. Create one that is the count of unique customers (this will also serve as your total), then from that measure created for each of your months, then create one for the count of months.

[Joop_de_Bont]

Hi Audrey,

Thanks for the suggestions, but how does these measure will look like?
(then from that measure created for each of your months, then create one for the count of months.)

Unique Customer Measure I have [Unique Customers = countrows(DISTINCT('Fact Sales'[Customer ID]))]

The first step I already had, but problem is the last bit of the puzzle. 

How does those month measure look like and how do I visualize in my report like my example?