Do I have to wait for measures in rows or is there another way?

[Greg_Deckler]

Quick question,

I have source data in the following format:

StoreNumber	Transdate	NetComp	NetSales	InternetNetSales	TotalOrders	Discounts
100	1/24/2017 0:00	0.00	1000.58	200.32	131	100.66
100	1/25/2017 0:00	0.00	1500.76	253.15	118	10068
100	1/26/2017 0:00	0.00	2500.43	250.54	109	120.12
100	1/27/2017 0:00	0.00	7000.83	700.25	259	250.72
100	1/28/2017 0:00	0.00	7500.62	900.33	248	250.1
100	1/29/2017 0:00	0.00	5000.23	630.79	174	200.63
100	1/30/2017 0:00	0.00	2400.40	250.76	100	170.73
100	1/31/2017 0:00	0.00	2,489.22	322.17	122	90.38
100	2/1/2017 0:00	0.00	3,907.32	393.55	134	250.12
100	2/2/2017 0:00	0.00	3,326.48	214.02	155	150.69

I need to calculate some measures based on these and addition numbers (more columns not shown here). However, the desired display is in the format:

	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday
Measure 1	###	###	###	###	###	###
Measure 2	###	###	###	###	###	###
Measure 3	###	###	###	###	###	###
Measure 4	###	###	###	###	###	###

I can create the measures easy enough and obviously get the weekday.

Do I have to wait for the planned idea, measures as rows, here:

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/10948962-matrix-measures-or-value...

Or does someone have a way that I might be able to accomplish this using some tricky pivoting/unpivoting/grouping of data in the query?

[Sean]

At the very bottom of this link from (March 8, 2017) there's a NEW survey

https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-matrix-visual/

that was one of the questions

But I in the meantime I think there's a hack I've seen from @MattAllington or @ImkeF

[Vvelarde]

@Greg_Deckler

A solution (not even closer the best) would be:

1. Create the measures. 

2. Create a table with the measures Name:

3. Create a measure :

MeasuretoShow =
IF (
    HASONEVALUE ( 'MeasureTable'[Measures] ),
    SWITCH (
        VALUES ( 'MeasureTable'[Measures] ),
        "Internet Sales"; [MInternetSales],
        "Total Orders"; [MTotalOrders],
        "Discounts"; [MDiscounts],
        "Net Sales"; [MNetSales]
    )
)

4. In a Matrix

[Greg_Deckler]

@Vvelarde to the rescue again! Thanks, I remembered there was a trick to this! Only odd quirk, for some inexplicable reason doing it this way strips out the prescribed formatting through the UI, but the fix is easy enough, just use FORMAT to enforce formatting:

M Sales Measures to Show = 
IF(
     HASONEVALUE('Sales Measures'[Sales Measures]),
     SWITCH(
          VALUES('Sales Measures'[Sales Measures]),
          "Sales AUV",FORMAT([M Sales AUV],"Currency"),
          "Transactions",[M Transactions],
          "Average Net Check",FORMAT([M Average Net Check],"Currency"),
          "Online Net Sales %",FORMAT([M Online Net Sales %],"Percent")
	 )
)

[ImkeF]

Hi @Greg_Deckler - that's a pretty cool trick!

But when I applied it, the measure was then formatted as text and left-aligned  - no good for numbers.

Do you have any idea how to fix this?

[Sean]

@ImkeFThat's odd. Can you share the overall Measure formula?

[ImkeF]

Thx @Sean for having a look at it. I changed the measure to this:

Switch = IF(HASONEVALUE(IndividualAccountsLayout[IsAccounts]),SWITCH(VALUES(IndividualAccountsLayout[IsAccounts]), "No", FORMAT([Act], "Standard"), "Accounts", FORMAT([Plan], "Standard")))

... just to make sure that it cannot be due to different formats assigned.

[Sean]

@ImkeF

I'm sorry I can't duplicate this!

First I changed only 1 of my measures to Format as ''Standard" instead of "Currency" it lost the $ but still had the thousand separator and decimal point and still right justified.

Then I changed all 4 Measures to Format as "Standard" again same result % and $ disappear but thousand sepator and decimal remain and still right justified!

I replaced the Year I had (whole number) in the Columns with a text category field still same result.

@Vvelardeany ideas?

[Anonymous]

@ImkeF

would you mind to mail me your data and measures as I can't replicate it?

Have an idea but I'm not sure.

[ImkeF]

Sure - here we go:

https://www.dropbox.com/s/eqcpooduho6uzh4/PBI_SwitchAlignment.pbix?dl=0

Thank you guys!

[Sean]

Well @Anonymous that works too!

@ImkeFit seems you have 2 options

1) multiply *1 the [Neg] and remain with a table visual

OR

2) switch to a Matrix and you don't have to do anything

(this post was about Measures on Rows in a Matrix so I just assumed you were working in a Matrix)

[Anonymous]

Thank you @Sean

[ImkeF]

Hey guys - thx a ton for the solutions - and the deep dive into the subtle but effective differences between tables and matrices in PBI 🙂

[Anonymous]

@ImkeF - it was my pleasure!

[Anonymous]

Just added a "*1" to the negative value.

The alignment seems to be ok, but it is still text...

Measure = IF(HASONEVALUE(Table1[Column1]);
	SWITCH(
		VALUES(Table1[Column1]); 
		"1"; FORMAT([Neg]; "Standard")*1;
		"2"; FORMAT([Pos]; "Percent")))

[Sean]

This actually makes sense when you think about it.

That's because technically M Sales Measures to Show is a single Measure unto itself.

You can't have a single Measure display multiple Formats - at least not yet!

You can Format M Sales Measures to Show in the Modeling tab as 1 type of Format.

If all Measures contained within it were the same Format that would work great.

(if all were $ or all % etc...) However that would rarely (if ever) be the case.

Hopefully they'll address this soon!

[MattAllington]

Yes I wrote an article about this here http://exceleratorbi.com.au/measures-on-rows-here-is-how-i-did-it/ 

[Sean]

@Vvelarde  Yes that was it!

When all else fails use a Disconnected Table