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 ...
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. ğŸ™‚

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

 

  • PaulMac's avatar
    PaulMac
    Helper IV
    2 years ago

    Amazing! Thank you Ritaf1983 for saving my sanity

     

    Paul

    • 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

      • PaulMac's avatar
        PaulMac
        Helper IV
        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. ğŸ™‚