Incorrect Column Totals in Matrix showing

[maamirkhan2023]

Hi All 

Incorrect Column Totals in Matrix showing 

Here is the dax I have used still showing Incorrect Column Totals in Matrix showing 

VAR __table = SUMMARIZE(Q_Host_List_Detection_HOSTS,"__value",count(Q_Host_List_Detection_HOSTS[IP]))

RETURN

IF(HASONEVALUE(Q_Host_List_Detection_HOSTS[Asset Tags]),count(Q_Host_List_Detection_HOSTS[IP]),SUMX(__table,[__value]))

What will be the dax to show correct Column Total in Matrix table

 

[Ashish_Mathur]

Hi,

Share the download link of the PBI file.

[v-dineshya]

Hi @maamirkhan2023 ,

Thank you for reaching out to the Microsoft Community Forum.

Please follow below steps.

1. Created sample data based on your screenshot. please refer below.

2. In Power Query editor, I have done trasformation on month columns (Unpivot columns from Jan to july). Please refer below snap.

3. Created measure "Detection Count" with below DAX code.

Detection Count =

VAR SummaryTable =

    SUMMARIZE(

        'Host_Detections',              

        'Host_Detections'[Asset Tags],

        "Cnt", SUM('Host_Detections'[DetectionCount])

    )

RETURN

IF(

    HASONEVALUE('Host_Detections'[Asset Tags]),

    SUM('Host_Detections'[DetectionCount]),

    SUMX(SummaryTable, [Cnt])

)

4. Please refer output snap and attached PBIX file.

I hope this information helps. Please do let us know if you have any further queries.

Regards,

Dinesh

[v-dineshya]

Hi @maamirkhan2023 ,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.

Regards,

Dinesh

[v-dineshya]

Hi @maamirkhan2023 ,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.

Regards,

Dinesh

[v-dineshya]

Hi @maamirkhan2023 ,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.

Regards,

Dinesh

[maamirkhan2023]

same Issue I already replied 

[v-dineshya]

Hi @maamirkhan2023 ,

You are getting incorrect column totals in Matrix. In my response , I have provide screenshot and PBIX file, please refer below snap, it will show the correct column totals in Matrix.

For example ,if we consider "Tag1"  from "January" to "July" , the column total value is "843".

17+717+6+50+24+11+18= 843. I think this is your expected output. 

I hope this information helps. Please do let us know if you have any further queries.

Regards,

Dinesh

[maamirkhan2023]

I try this Dax  and your dax but same issue 

VAR SummaryTable =

    SUMMARIZE(

        'Q_Host_List_Detection_HOSTS',              

       Q_Host_List_Detection_HOSTS[Asset Tags],

        "Cnt", DISTINCTCOUNT(Q_Host_List_Detection_HOSTS[IP])

    )

RETURN

IF(

    HASONEVALUE(Q_Host_List_Detection_HOSTS[Asset Tags]),

    DISTINCTCOUNT(Q_Host_List_Detection_HOSTS[IP]),

    SUMX(SummaryTable, [Cnt])

)

[v-dineshya]

Hi @maamirkhan2023 

Once again i am sending the PBIX file . Please check the file.

I hope this information helps. Please do let us know if you have any further queries.

Regards,

Dinesh

[maamirkhan2023]

same issue  incorrect column totals in Matrix. 

[v-dineshya]

Hi @maamirkhan2023 ,

Please try below DAX code.

VAR SummaryTable =
SUMMARIZE(
'Q_Host_List_Detection_HOSTS',
Q_Host_List_Detection_HOSTS[Asset Tags],
"Cnt", SUM(Q_Host_List_Detection_HOSTS[IP])
)
RETURN
IF(
HASONEVALUE(Q_Host_List_Detection_HOSTS[Asset Tags]),
SUM(Q_Host_List_Detection_HOSTS[IP]),
SUMX(SummaryTable, [Cnt])
)

I hope this information helps. Please do let us know if you have any further queries.

Regards,

Dinesh

[maamirkhan2023]

Not working and I cannot use Sum here as I mention above many times

[v-dineshya · Accepted solution]

Hi @maamirkhan2023 ,

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot). Do not include sensitive information. Do not include anything that is unrelated to the issue or question. Please show the expected outcome based on the sample data you provided. If possible please provide sample PBIX file.

Regards,
Dinesh

[maamirkhan2023]

Table 1 contails Tags and IP
Table 2 contains Date and I joined with ID and created Matrix table

VAR SummaryTable =

    SUMMARIZE(

        'Q_Host_List_Detection_HOSTS',              

       Q_Host_List_Detection_HOSTS[Asset Tags],

        "Cnt", DISTINCTCOUNT(Q_Host_List_Detection_HOSTS[IP])

    )

RETURN

IF(

    HASONEVALUE(Q_Host_List_Detection_HOSTS[Asset Tags]),

    DISTINCTCOUNT(Q_Host_List_Detection_HOSTS[IP]),

    SUMX(SummaryTable, [Cnt])

)

[v-dineshya]

Hi @maamirkhan2023 ,

Please provide sample data and  expected output. it will help me to replicate the scenario. without sample data and without expected output, we can't proceed further.

Regards,

Dinesh

[maamirkhan2023]

Yes but Where is PBIX file can you please share again and what will be dax

[maamirkhan2023]

Where is PBIX file can you please share again

[grazitti_sapna]

Hi @maamirkhan2023,

Issue with your DAX is SUMX(__table, [__value]) — This is referencing a column [__value] that doesn't actually exist outside the SUMMARIZE. You're not creating a real column called __value, and SUMX doesn’t know how to evaluate [__value].

You can try this:

Correct IP Count =
VAR __table =
SUMMARIZE(
Q_Host_List_Detection_HOSTS,
Q_Host_List_Detection_HOSTS[Asset Tags],
"IPCount", COUNT(Q_Host_List_Detection_HOSTS[IP])
)
RETURN
IF(
HASONEVALUE(Q_Host_List_Detection_HOSTS[Asset Tags]),
COUNT(Q_Host_List_Detection_HOSTS[IP]),
SUMX(__table, [IPCount])
)

🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!

[maamirkhan2023]

Still same issue as above screenshot

[grazitti_sapna]

Hi @maamirkhan2023,

Let's try other ways to fix it,

Instead of COUNT() or DISTINCTCOUNT() directly, wrap it inside SUMX for row-level precision

Correct IP Count =
IF(
HASONEVALUE(Q_Host_List_Detection_HOSTS[Asset Tags]),
CALCULATE(COUNT(Q_Host_List_Detection_HOSTS[IP])),
SUMX(
VALUES(Q_Host_List_Detection_HOSTS[Asset Tags]),
CALCULATE(COUNT(Q_Host_List_Detection_HOSTS[IP]))
)
)

Alternate: DISTINCT IPs per Asset Tag

Correct Distinct IPs =
IF(
HASONEVALUE(Q_Host_List_Detection_HOSTS[Asset Tags]),
DISTINCTCOUNT(Q_Host_List_Detection_HOSTS[IP]),
SUMX(
VALUES(Q_Host_List_Detection_HOSTS[Asset Tags]),
CALCULATE(DISTINCTCOUNT(Q_Host_List_Detection_HOSTS[IP]))
)
)

If still it's not working try with this,

Correct IP Count =
IF(
ISINSCOPE(Q_Host_List_Detection_HOSTS[Asset Tags]),
COUNT(Q_Host_List_Detection_HOSTS[IP]),
SUMX(
VALUES(Q_Host_List_Detection_HOSTS[Asset Tags]),
CALCULATE(COUNT(Q_Host_List_Detection_HOSTS[IP]))
)
)

Mark this as a solution if the issue get's fixed