Need to add extra column in existing Power BI matrix visual

[DataSpace]

I can not post customer data , hence tried to create exact scenario here.

I have to create a PBI report with Matrix visual which (later) supports DrillThrough action.

Below is what I want to achieve:

I tried to create dataset(below) to support above report only for row no 1 and 5.

in report above, I am able to achieve till column H, where it is showing count of Brands sold.

But irrespective of Brand sold , requirement is also to include count of "in-progress" status just like it is shown column I (In-Progress column). And this is where I am struggling.

I tried creating measure and calculated column to count "in-progress" status separately and then drag it to values Or then Columns, Both are not working and giving weird result.

Any help/comment is appreciated. OR is there any other way to achieve it like with Table visuals considering drillthough should not break?

thanks

[KNP · Accepted solution]

Hey @DataSpace,

Difficult if not impossible to achieve in the standard matrix. 

I've done what I think you're after in a table. See attached PBIX.

Note: could probably be done cleaner with Calculation Groups but I'm time-poor.

If you've got the November release of Power BI Desktop, you can just post the below into the DAX query view and change table/column names as required and add measures from there.

DEFINE
    MEASURE 'Transportation'[In-progress] = CALCULATE (
    COUNTA ( 'Transportation'[Status] ),
    'Transportation'[Status] = "In-progress",
    REMOVEFILTERS ( Transportation[Brand Sold] )
)
    MEASURE 'Transportation'[BMW] = CALCULATE (
    COUNTA ( Transportation[Status] ),
    Transportation[Brand Sold] = "BMW"
)
    MEASURE 'Transportation'[Suzuki] = CALCULATE (
    COUNTA ( Transportation[Status] ),
    Transportation[Brand Sold] = "Suzuki"
)
    MEASURE 'Transportation'[Honda] = CALCULATE (
    COUNTA ( Transportation[Status] ),
    Transportation[Brand Sold] = "Honda"
)
    MEASURE 'Transportation'[Yamaha] = CALCULATE (
    COUNTA ( Transportation[Status] ),
    Transportation[Brand Sold] = "Yamaha"
)

EVALUATE
    SUMMARIZECOLUMNS(
		Transportation[Product Category],
		Transportation[Product Sub Category],
        "BMW", 'Transportation'[BMW],
        "Suzuki", 'Transportation'[Suzuki],
        "Honda", 'Transportation'[Honda],
        "Yamaha", 'Transportation'[Yamaha],
        "In-progress", 'Transportation'[In-progress]
    )

Hope this helps.

[DataSpace]

Thanks both of you @danextian  and @KNP  for your quick reply.

Let me please check and implement the solution.

Thanks Again

[danextian · Accepted solution]

Hi @DataSpace ,

If you want an extra column in your matrix, you will need to make that extra column exist using a disconnected table which will hold brands as well as another row  for in-progress then use create measure to return your desired value. Please see attached pbix for details.

[KNP · Accepted solution]

Hey @DataSpace,

Difficult if not impossible to achieve in the standard matrix. 

I've done what I think you're after in a table. See attached PBIX.

Note: could probably be done cleaner with Calculation Groups but I'm time-poor.

If you've got the November release of Power BI Desktop, you can just post the below into the DAX query view and change table/column names as required and add measures from there.

DEFINE
    MEASURE 'Transportation'[In-progress] = CALCULATE (
    COUNTA ( 'Transportation'[Status] ),
    'Transportation'[Status] = "In-progress",
    REMOVEFILTERS ( Transportation[Brand Sold] )
)
    MEASURE 'Transportation'[BMW] = CALCULATE (
    COUNTA ( Transportation[Status] ),
    Transportation[Brand Sold] = "BMW"
)
    MEASURE 'Transportation'[Suzuki] = CALCULATE (
    COUNTA ( Transportation[Status] ),
    Transportation[Brand Sold] = "Suzuki"
)
    MEASURE 'Transportation'[Honda] = CALCULATE (
    COUNTA ( Transportation[Status] ),
    Transportation[Brand Sold] = "Honda"
)
    MEASURE 'Transportation'[Yamaha] = CALCULATE (
    COUNTA ( Transportation[Status] ),
    Transportation[Brand Sold] = "Yamaha"
)

EVALUATE
    SUMMARIZECOLUMNS(
		Transportation[Product Category],
		Transportation[Product Sub Category],
        "BMW", 'Transportation'[BMW],
        "Suzuki", 'Transportation'[Suzuki],
        "Honda", 'Transportation'[Honda],
        "Yamaha", 'Transportation'[Yamaha],
        "In-progress", 'Transportation'[In-progress]
    )

Hope this helps.

[DataSpace]

Hi @KNP , your solution also works but to make the drill through work perfectly, I will have to add all the created measures in detailed table  and these measures should not be visible to end user.

And in the solution from @danextian , I only need to add one measure which can be hidden by reducing the width size.
Thanks both of you for quick support.