Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Icon for Administrator rankAdministrator
1 year ago
Solved

Array does not sum data correctly

Hello I have a matrix to present some data. The array has several levels, the problem is that when I start expanding the levels, the values don't add up and don't match the total, I attach a screens...
  • v-ssriganesh's avatar
    v-ssriganesh
    1 year ago

    Hi Syndicate_Admin,
    Thank you for reaching out to the Microsoft Fabric Community Forum.

    I have reproduced your issue in Power BI Desktop and implemented the required logic to handle the (Blank) roles by replacing them with "Unassigned".

    What I did:

    • Created a new calculated column using the following DAX:
    RoleFixed = IF(

    ISBLANK('EnrollmentData'[Role]),

    "Unassigned",

    'EnrollmentData'[Role]

)
    • Used the new RoleFixed column in the Matrix visual, and it correctly displays "Unassigned" instead of blank for roles.
    • Verified the total enrolled counts match the original data.

    Expected output:

    This meets the expected outcome based on your requirement.

    For your reference, I’ve attached the working .pbix file.

    If this information is helpful, please “Accept as solution” and give a "kudos" to assist other community members in resolving similar issues more efficiently.
    Thank you.

pankajnamekar25's avatar
pankajnamekar25
Icon for Super User rankSuper User
1 year ago

Hello Syndicate_Admin 

Try this measure

AdjustedCount =

IF(

    HASONEVALUE('Roles'[RoleName]),

    COUNT('Table'[UserID]),  -- detail level

    SUMX(

        VALUES('Roles'[RoleName]),

        CALCULATE(COUNT('Table'[UserID]))

    )

)

Thanks,
 Pankaj Namekar | LinkedIn

If this solution helps, please accept it and give a kudos (Like), it would be greatly appreciated.