Getting max value at the column subtotal instead of sum

[Mpcz]

Hi All,

I'm seeking for help in understanding a certain behaviour of matrix and the dax formula I'm using.

Here is the formula which I'm using to check and count the occurences of table 1 rows in table 2:

I'm presenting the results in matrix with the following hierarchy= Category->Subcategory->Product, therefore the use of "ISINSCOPE" in above formula. At the lowest level I'm getting the numbers of products occurences in certain dates with sum in the column subtotal and that's the actual behaviour I'm seeking for. At the Subcategory and Category levels I only need to know if those occured in the second table on a certain date I don't need the count of occurences therefore i modified the "IF" statments to return "1" instead of results of "COUNTROWS"

but the issues is that the returned "1" are not summarizing in the column subtotal it seems to return the MAX value of the row.

Product level:

Subcategory level:

Could you please help me understand how to get the SUM at the column subtotal?

Than you in advance for the help.

[Greg_Deckler]

@Mpcz So, to get total and subtotal's correct you generally have to use SUMMARIZE or GROUPBY. Hard to really tell what is going on with the information provided. MM3TR&R might help you out: (1) Matrix Measure Total Triple Threat Rock & Roll - Microsoft Power BI Community

[Mpcz]

@Greg_Deckler Hi Greg, I will try to provide more details so maybe it will be easier to understand. Thank you for the provided link but it's a bit hard for me to understand and possibly re-apply.

I have 2 tables, one containing 3 columns with all the products, subcategories and categories. Second table contains same 3 columns + column with a date on which certain product has been sold(so certain product,subcategory and category appears in this table with a timestamp only if it has been sold on a certain date). What i need to achieve is to show in matrix if certain product has been sold on a certain date or not, therefore I checked if the entries of table 1 exist in table 2 with the below dax formula,

as well I want to have a posibility to drill up matrix and see if categories and subcategories has appeared on a certain day in table 2. The above formula is doing its job and its counting the occurences of subcategories and categories but the output I need is "0" if the subcategory or category did not appear on a certain date (which works fine with the current formula) but if it did appear I need only "1" not the actual count of all the occurences on a certain date, therefore I modified the below part to return "1" if a subcategory or category from table 1 has been found in table 2.

The issue is that if I drill up to the subcategory or category level the column subtotal is equal "1" so it's seems to be returning the maximum value of the row instead of the sum of 1's, as shown below:

For the lowest level, which is product it works fine, as well its the level where I haven't changed the false statement so it's returning the actual rowcount instead of "forcing" 1 as result for a certain day: