Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Mpcz
Frequent Visitor

Getting max value at the column subtotal instead of sum

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:

Mpcz_5-1660741605235.png

 

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"

Mpcz_2-1660741368554.png

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:

Mpcz_3-1660741452728.png

Subcategory level:

Mpcz_4-1660741478604.png

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

 

Than you in advance for the help.

2 REPLIES 2
Greg_Deckler
Super User
Super User

@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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

@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,

Mpcz_0-1660818929621.png

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.

Mpcz_2-1660819297641.png

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:

Mpcz_5-1660819719749.png

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:

Mpcz_6-1660819852077.png

 

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors