Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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:
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?
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
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
@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") ) )
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?
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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?
would you mind to mail me your data and measures as I can't replicate it?
Have an idea but I'm not sure.
Sure - here we go:
https://www.dropbox.com/s/eqcpooduho6uzh4/PBI_SwitchAlignment.pbix?dl=0
Thank you guys!
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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)
Hey guys - thx a ton for the solutions - and the deep dive into the subtle but effective differences between tables and matrices in PBI 🙂
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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")))
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!
Yes I wrote an article about this here http://exceleratorbi.com.au/measures-on-rows-here-is-how-i-did-it/