Forum Discussion

PaulMac's avatar
PaulMac
Helper IV
2 years ago
Solved

Need Help Formatting Matrix Table

Hi

 

I have created a matrix table which lists Job Numbers along with a measure counting the occurances of the Job Numbers. I want to have it so that the Job Number can be expanded to show all the relevant Letter Numbers associated with that job.
This I have done, however when I expand the job number row the DAX value is showing on the Letter Number rows and is causing some confusion for users.
As per the example below, I would like for it to be, that I only see the DAX value on the Job Number row (Red), with the nested Letter Number (yellow) rows to show as blank instead of 3. I've been tinkering with this all week and struggling to find a usable solution. Posed my query to chatGPT and CoPilot, neither of which could find a workable solution.



Thanks for reading

 

Need Help
  • Ritaf1983's avatar
    Ritaf1983
    2 years ago

    Hi PaulMac 
    You can manipulate what to show by the hierarchy level with "Isinscope" function.
    Like :

    The pbix with the example is attached 

    If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

     

  • PaulMac's avatar
    PaulMac
    2 years ago

    Thanks for your swift reply Ritaf1983 

    I did however find a solution to my problem, I needed to elaborate the DAX and use the SUMMARIZE function in order for the Total to show correctly for me.
    My DAX ended up looking like this:

    CountJobs_by_Volume = 
VAR countVol = 
    CALCULATE(
        COUNTA(Usage[JobNumber]),
        FILTER(
            Usage,
            Usage[JobNumber] = SELECTEDVALUE(Jobs_Letters_and_DAS[JobNumber])
        )
    )

VAR totalVol =
    SUMX(
        SUMMARIZE(
            Jobs_Letters_and_DAS, 
            Jobs_Letters_and_DAS[JobNumber],
            "JobCount", 
            CALCULATE(COUNTA(Usage[JobNumber]))
        ),
        [JobCount]
    )

RETURN
IF(
    ISINSCOPE(Jobs_Letters_and_DAS[Letter No]),
    BLANK(),
    IF(
        HASONEVALUE(Jobs_Letters_and_DAS[JobNumber]),
        countVol,
        totalVol
    )
)


    Your help has been very much appreciated. ğŸ™‚

7 Replies

  • Ritaf1983's avatar
    Ritaf1983
    Super User
    2 years ago

    Hi PaulMac 
    You can manipulate what to show by the hierarchy level with "Isinscope" function.
    Like :

    The pbix with the example is attached 

    If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

     

      • Ritaf1983's avatar
        Ritaf1983
        Super User
        2 years ago

        PaulMac 
        Happy to help 🙂

        If previous post helped, then please consider Accepting it as the solution to help the other members find it more quickly

    • PaulMac's avatar
      PaulMac
      Helper IV
      2 years ago

      Hi Ritaf1983 
      I have noticed that my Total row doesn't appear correct and is showing a much lower number than expected. I see on your example your Total appears un affected, can I ask how you achieved this?
      Thanks
      Paul