Use a measure as column in a matrix

[rpinxt]

I have this data (more colums but these matter for the matrix) with a measure "Flag" with help of @v-rzhou-msft 

As said Flag is a measure but I want to make a matrix in which I want to see the % Late and on Time based on the quantity.

Something like :

So in the columns I would like to split Qty to "Late" and "On Time".

Then I could show quantity as a percentage and I would have the split that we want.

Also in the rows we probably would like to put shipmentnr and delivery document to split even into more detail.

But the column will not accept the measure "Flag" as input.

Below a sample set with data from 3 months for testing:

Period	ShipmentNr	Delivery Document Number	Qty	HourDiff	Flag
Jan-24	183117	80842784	2475	1.57	On Time
Jan-24	183117	80842785	2070	2.05	On Time
Jan-24	183117	80842786	2475	2.67	On Time
Jan-24	183117	80842787	2235	1.38	On Time
Jan-24	183117	80842788	2475	1.32	On Time
Jan-24	183117	80842799	2475	2.45	On Time
Jan-24	183118	80842858	130	5.18	On Time
Jan-24	183118	80842859	347	2.52	On Time
Jan-24	183118	80842860	280	3.28	On Time
Jan-24	183118	80842861	240	5.4	On Time
Jan-24	183118	80842864	123	5.17	On Time
Jan-24	183118	80842865	149	1.88	On Time
Jan-24	183118	80842866	120	2.62	On Time
Jan-24	183118	80842867	240	4.87	On Time
Jan-24	183118	80842872	1150	2.48	On Time
Jan-24	186533	80847728	180	24.65	Late
Jan-24	186533	80847731	180	24.67	Late
Jan-24	186533	80847732	180	24.75	Late
Feb-24	187085	80848396	70	18.77	On Time
Feb-24	187085	80848397	88	18.77	On Time
Feb-24	187085	80848398	120	18.97	On Time
Feb-24	187085	80848400	50	25.43	Late
Feb-24	187086	80848472	480	18.42	On Time
Feb-24	187086	80848473	90	20.78	On Time
Feb-24	187086	80848474	480	18.92	On Time
Feb-24	187086	80848475	480	18.93	On Time
Feb-24	187086	80848608	198	21.33	On Time
Feb-24	187086	80848609	210	21.28	On Time
Feb-24	187086	80848610	1180	21.27	On Time
Feb-24	187086	80848658	180	18.95	On Time
Feb-24	187086	80848659	180	20.23	On Time
Feb-24	187086	80848660	180	19	On Time
Feb-24	187086	80848661	180	20.98	On Time
Feb-24	187086	80848662	180	20.1	On Time
Feb-24	187086	80848663	180	21.4	On Time
Feb-24	187086	80848664	180	20.28	On Time
Feb-24	189882	80851996	31	27.8	Late
Mar-24	192666	80854016	305	2.15	On Time
Mar-24	192666	80854480	1080	0.53	On Time
Mar-24	192666	80854877	2	2.17	On Time
Mar-24	192666	80854881	170	2.13	On Time
Mar-24	192666	80854882	615	2.43	On Time
Mar-24	192666	80854883	100	2.47	On Time
Mar-24	192666	80854884	160	2.48	On Time
Mar-24	192666	80854885	140	2.07	On Time
Mar-24	192666	80854886	240	2.48	On Time
Mar-24	192666	80854887	20	1.12	On Time
Mar-24	192666	80854888	106	2.18	On Time
Mar-24	192666	80854889	255	2.1	On Time
Mar-24	192666	80854890	745	2.13	On Time
Mar-24	192666	80854891	1	0.48	On Time
Mar-24	192666	80854892	1	2.45	On Time
Mar-24	192733	80855634	1845	0.13	On Time
Mar-24	192733	80855636	990	0.08	On Time
Mar-24	192733	80855637	975	0.12	On Time
Mar-24	192733	80855838	280	0.9	On Time
Mar-24	192733	80855839	231	0.08	On Time
Mar-24	192733	80855840	152	1.18	On Time
Mar-24	192733	80855841	605	0.37	On Time
Mar-24	192733	80855848	275	0.82	On Time
Mar-24	192953	80855893	121	0.12	On Time
Mar-24	192953	80856197	321	0.27	On Time
Mar-24	193580	80856689	1980	1.55	On Time
Mar-24	193580	80856691	1980	1.07	On Time
Mar-24	193580	80856693	1845	1.2	On Time
Mar-24	193580	80856719	1050	0	On Time
Mar-24	193580	80856721	1080	0	On Time
Mar-24	193580	80856722	1080	0.5	On Time
Mar-24	193580	80856723	1080	0.3	On Time
Mar-24	193580	80856724	1080	0.8	On Time
Mar-24	194646	80857254	26	24.12	Late
Mar-24	194646	80857958	255	24.1	Late
Mar-24	194875	80858343	10	71.98	Late
Mar-24	197533	80862389	1050	26.13	Late

[rpinxt · Accepted solution]

Well still like to know how this works with a measure, but I now was able to merge my 2 sources together.

Having 1 table gave me the opportunity to make calculated columns :

Of course now I have a big advantage for having real fields on which I can filter!

Now putting Timing (former called 'Flag') in a slicer or in a matrix is super easy :

So I will keep working with these calculated columns but still interested if it also can work with measures

[Greg_Deckler · Accepted solution]

@rpinxt In general, to use a measure in that way, you need to use the Disconnected Table Trick as this article demonstrates: https://community.powerbi.com/t5/Community-Blog/Solving-Attendance-with-the-Disconnected-Table-Trick...

[rpinxt · Accepted solution]

Well still like to know how this works with a measure, but I now was able to merge my 2 sources together.

Having 1 table gave me the opportunity to make calculated columns :

Of course now I have a big advantage for having real fields on which I can filter!

Now putting Timing (former called 'Flag') in a slicer or in a matrix is super easy :

So I will keep working with these calculated columns but still interested if it also can work with measures

[Greg_Deckler · Accepted solution]

@rpinxt In general, to use a measure in that way, you need to use the Disconnected Table Trick as this article demonstrates: https://community.powerbi.com/t5/Community-Blog/Solving-Attendance-with-the-Disconnected-Table-Trick...

[rpinxt]

Thanks @Greg_Deckler I will study this.

[danextian]

Hi @rpinxt ,

How do you calculate Flag?

[rpinxt]

Hi @danextian this is used for Flag:

Flag =

VAR _HOURDIFF = DIVIDE(DATEDIFF(MAX('Shipment ATA'[ATA]),MAX(SC1[D&T]),MINUTE),60)

RETURN

IF(NOT ISBLANK([HourDiff]),

    IF(_HOURDIFF>24,"Late","On Time")

)

[danextian · Accepted solution]

You'll need a disconnected table (no relationship to other tables) that has a column with  values Late and On Time. You can use Enter data to create that.

Assuming that the flag is to be calculated by ShipmentNr and Document Number, you'll need to create another measure to return desired result baased on the value of the column from the disconnected table. Example:

VAR Late_OnTime =
    SELECTEDVALUE ( disconnectedtable[columm] )
RETURN
    SWITCH (
        Late_OnTime = "Late",
        SUMX (
            ADDCOLUMNS (
                ADDCOLUMNS (
                    SUMMARIZE (
                        'Shipment ATA',
                        'Shipment ATA'[ATA],
                        'Shipment ATA'[D&T],
                        'Shipment ATA'[ShipmentNr],
                        'Shipment ATA'[Document Number]
                    ),
                    "@HourDiff", [HourDiff]
                ),
                "@Flag",
                    VAR __HOURDIFF =
                        DIVIDE ( DATEDIFF ( [ATA], [D&T], MINUTE ), 60 )
                    RETURN
                        IF ( NOT ISBLANK ( [@HourDiff] ) && __HOURDIFF > 24, 1, 0 )
            ),
            [@Flag]
        ), "OnTime",
        SUMX (
            ADDCOLUMNS (
                ADDCOLUMNS (
                    SUMMARIZE (
                        'Shipment ATA',
                        'Shipment ATA'[ATA],
                        'Shipment ATA'[D&T],
                        'Shipment ATA'[ShipmentNr],
                        'Shipment ATA'[Document Number]
                    ),
                    "@HourDiff", [HourDiff]
                ),
                "@Flag",
                    VAR __HOURDIFF =
                        DIVIDE ( DATEDIFF ( [ATA], [D&T], MINUTE ), 60 )
                    RETURN
                        IF ( NOT ISBLANK ( [@HourDiff] ) && __HOURDIFF <= 24, 1, 0 )
            ),
            [@Flag]
        )
    )

The above formula assumes that you are going to count the late and on time rows at the granularity level of of ShipmentNr and Document Number.
By the way, have you tried using Field Parameters? You'll still need to create a separate measure for Late and On Time.

[rpinxt]

Great @danextian !

Much more complicated than using calculated columns 😉

But still good to see this and learn from it.

Thanks!