Forum Discussion
Helper IIncorrect Column Totals in Matrix showing
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
Super UserHi 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!
Helper IStill same issue as above screenshot
- grazitti_sapna
Hi maamirkhan2023,
Let's try other ways to fix it,
Instead of
COUNT()orDISTINCTCOUNT()directly, wrap it inside SUMX for row-level precisionCorrect 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
- maamirkhan2023
same issue
- maamirkhan2023
I use this dax but same issue
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]))))