Forum Discussion
Helper IIDisplay measures in rows with columns as time intelligence measures
Hi,
I would like to display measures in rows. That is clear how to do it if columns are "normal" dimensions. But I want following columns (for all chosen measures) :
Col 1: day (or date) value
Col 2: month-to-date
Col 3: Year-to date
Col 4: Month-to-date last year
Col 5: Year-to-date last year
Col 6: Month-to-date Total (let't say matrix is for one market, and this column should be for all markets)
Something like this:
| Date | Month-to-date | MTD last year | Year-to-date | YTD last year | MTD total market | |
| Quantity | ||||||
| Sales | ||||||
| Costs | ||||||
| Margin |
Where Quantity, sales, costs and margin are all measures.
The problem is (at least to me) that matrix consist of measures only. There are no dimensions. Columns are derivative measures of row measures. How can I put in matrix measures and their YTD, Last year etc.?
Are there any custom visual that supports this? Or some DAX?
Thank you!
Zrinko
Hi Dale,
What I meant, everything are actuals for measures in rows. MEasures are Quantity, Sales etc. And columns are actual value for those measures for a single day, for month-do-date, for year-to date, for last year . I.e time intelligence functions of basic measures.
Here is the example:
Date (in Slicer) 8.3.2018. Date actual Month-to-date actual MTD last year actual Year-to-date actual YTD last year actual Quantity 50 350 358 3120 3189 Sales 500 3785 4000 31289 32568 Costs 300 2452 2600 17862 19252 Margin 200 1333 1400 13427 13316 I think I solved it.
I created auxilirary table with measure names (Table is 'Popis mjera'; column name is [Naziv mjere]; column values (actual measure names) are Prodaja, SL_prodaja etc)
And then several new measures with switch.
Here is an example:
Realizacija = SWITCH(TRUE(); CALCULATETABLE(VALUES('Popis mjera'[Naziv mjere]); 'Popis mjera'[Naziv mjere] = "Prodaja") = "Prodaja" ; [Prodaja]; CALCULATETABLE(VALUES('Popis mjera'[Naziv mjere]); 'Popis mjera'[Naziv mjere] = "Slobodna prodaja") = "Slobodna prodaja" ; [Sl_Prodaja]; CALCULATETABLE(VALUES('Popis mjera'[Naziv mjere]); 'Popis mjera'[Naziv mjere] = "Receptna prodaja") = "Receptna prodaja" ; [Rec_Prodaja]; CALCULATETABLE(VALUES('Popis mjera'[Naziv mjere]); 'Popis mjera'[Naziv mjere] = "Doplata RX") = "Doplata RX" ; [Doplata RX]; CALCULATETABLE(VALUES('Popis mjera'[Naziv mjere]); 'Popis mjera'[Naziv mjere] = "Ostala prodaja") = "Ostala prodaja" ; [Ostala prodaja] )Measure and table names are in Croatian, but I believe you'll get the clue :)
I created measures for YTD, LY and repeated SWITCH with corresponding measures. It works.
Two questions though:
1. is it possible to solve it easier, simpler? I mean on a calculatetable part. I wanna extract one value from 'Popis mjera' table.
2. What if one measure does not exist for a chosen period (whole row returns blanks) and I still wanna show 0 instead in that row? I know I can test whole Switch on ISBLANK(), but that would be rather awkward formula.
Many thanks.
Zrinko
6 Replies
v-jiascu-msftMicrosoft Employee
Hi zvm,
Can you share dummy sample please?
Do you mean the columns and the rows are all measures?
How to fill the data in your desired visual? Seems the following table doesn't mean anything.
Date Month-to-date MTD last year Year-to-date YTD last year MTD total market Quantity 2018-01-01 100 Sales 2018-01-02 200 Costs 2018-01-03 200 Margin 2018-01-04 200 Best Regards,
Dale
zvmHi Dale,
What I meant, everything are actuals for measures in rows. MEasures are Quantity, Sales etc. And columns are actual value for those measures for a single day, for month-do-date, for year-to date, for last year . I.e time intelligence functions of basic measures.
Here is the example:
Date (in Slicer) 8.3.2018. Date actual Month-to-date actual MTD last year actual Year-to-date actual YTD last year actual Quantity 50 350 358 3120 3189 Sales 500 3785 4000 31289 32568 Costs 300 2452 2600 17862 19252 Margin 200 1333 1400 13427 13316 I think I solved it.
I created auxilirary table with measure names (Table is 'Popis mjera'; column name is [Naziv mjere]; column values (actual measure names) are Prodaja, SL_prodaja etc)
And then several new measures with switch.
Here is an example:
Realizacija = SWITCH(TRUE(); CALCULATETABLE(VALUES('Popis mjera'[Naziv mjere]); 'Popis mjera'[Naziv mjere] = "Prodaja") = "Prodaja" ; [Prodaja]; CALCULATETABLE(VALUES('Popis mjera'[Naziv mjere]); 'Popis mjera'[Naziv mjere] = "Slobodna prodaja") = "Slobodna prodaja" ; [Sl_Prodaja]; CALCULATETABLE(VALUES('Popis mjera'[Naziv mjere]); 'Popis mjera'[Naziv mjere] = "Receptna prodaja") = "Receptna prodaja" ; [Rec_Prodaja]; CALCULATETABLE(VALUES('Popis mjera'[Naziv mjere]); 'Popis mjera'[Naziv mjere] = "Doplata RX") = "Doplata RX" ; [Doplata RX]; CALCULATETABLE(VALUES('Popis mjera'[Naziv mjere]); 'Popis mjera'[Naziv mjere] = "Ostala prodaja") = "Ostala prodaja" ; [Ostala prodaja] )Measure and table names are in Croatian, but I believe you'll get the clue :)
I created measures for YTD, LY and repeated SWITCH with corresponding measures. It works.
Two questions though:
1. is it possible to solve it easier, simpler? I mean on a calculatetable part. I wanna extract one value from 'Popis mjera' table.
2. What if one measure does not exist for a chosen period (whole row returns blanks) and I still wanna show 0 instead in that row? I know I can test whole Switch on ISBLANK(), but that would be rather awkward formula.
Many thanks.
Zrinko
