Need Help Formatting Matrix Table

[PaulMac]

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

[Ritaf1983 · Accepted solution]

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 · Accepted solution]

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]

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

[Ritaf1983]

Hi @PaulMac 

Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

[PaulMac · Accepted solution]

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]

Great , it's my honor ♥️

[PaulMac]

Amazing! Thank you @Ritaf1983 for saving my sanity

Paul

[Ritaf1983]

@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