How do I get the row totals to correct add up?

[ccolletti]

Hi All,

I'm working on building a Matrix table for a report that pulls in our Vendors Name, Item Name and Type with how many times it shows up on a lists throughout specific fiscal weeks. When I load in a count of either the Item Name or Number I will see the totals add up for the Vendors subtotal, but not at the actual Item Name and Type level, making the subtotal incorrect.

Looking at the data within the red box it shows the subtotals in bold, which would be correct. However with the highlighted line, the bold total on the right shows "1" instead of  what should be "2". How can I get this to read as "2" instead of "1"?

I've attempted to build a measure to count the Item Names, but I still get the same totals when I add it to the Values of the visual.

Thank you so much for your help!

[rubayatyasmin]

Hi, @ccolletti 

It is a common issue in Power BI, Measure total is broken. Greg_Deckler has raised an issue, if you did not vote for  it yet, might want to vote for it. 

Try this sample code to solve your problem. 

Item Count = COUNTAX(ALLSELECTED('Table'[Item Name]), [Item Name])

please adjust the column name as per your table. 

[ccolletti]

Thanks for responding @rubayatyasmin unfortunetly that didn't seem to work. I got all the same numbers for every single cell.

Did play around with the query (just can't rember if I had tried it before) when I looking into the COUNTX forumula and got the same type of data as I did before, so it's still not correctly adding the row numbers together: ITEM_COUNT = COUNTAX('Table',[ITEM_NAME])

Is there a link to where I can vote for the issue raised by Greg_Deckler? Definetly want to since this should work in BI. Would be very helpful to not just me, but I know a lot of others. Thanks!

[rubayatyasmin]

Try this one.

Item Count = CALCULATE(
COUNT('Table'[Item Name]),
ALLEXCEPT('Table', 'Table'[Vendor Name], 'Table'[Item Name], 'Table'[Item Type])
)

if this doesn't work try using earlier DAX function. With ALL

@Greg_Deckler please provide your link to vote for measure total issue. 

[ccolletti]

Unfortunetly, I'm still getting the same incorrect totals. Also I wasn't able to use that exact query as the Vendor Name is actually coming from a different table. Wasn't able to pull in the Item Name and Type after specifying the second table.

CALCULATE(COUNT('Table1'[Item Name]),ALLEXCEPT('Table2','Table2'[Vendor Name]))

[rubayatyasmin]

Can you please provide some sample data? Or a pbix file?

[ccolletti]

I'll have to work on getting some data for you, I just unfortunetly do not have access to the actual dataset.

Wasn't sure if this was just a setting issue, or something other formula I could use to get the data to add up to five the correct calculations?

[foodd]

Create, to the best of your abilities, a sample PBIX that is modeled in the way that you believe it to be true.  Set the model up as a Star Schema.  Create a Date table using power query-m that includes any offsets for Fiscal Periods that may not already be built for you.   I would suggest using the Extended Date Table that was developed by Melissa de Korte, Enterprise DNA Expert | Power BI.  It is based upon ISO weeks, is well known, very flexible, and extensible.   While you are there, take some time to search out the Community Forum with your question in mind.   It is much like the holidays; you never know what gift of learning is right around the corner.

Using The M Function To Create An Extended Power BI Date Table Code | Enterprise DNA

Extended Date Table Power Query M Function (enterprisedna.co)

The powerquery-m code is inline in this post:
Extended Date Table (Power Query M function) - Power Query / M Code Showcase - Enterprise DNA Forum

[foodd]

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot).

https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

This allows members of the Forum to assess the state of the model, report layer, relationships, and any DAX applied.