Display measures in rows with columns as time intelligence measures

[zvm]

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

[v-jiascu-msft]

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

[zvm · Accepted solution]

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

[Anonymous]

i have same problem please tell me how you bind these Values to which Visual of Power BI. Further What is Realizacija =?

is it Variable or table?

[zvm]

Hi,

Realizacija is measure. 

In this particular case, I am using Table Visual. So, depending on value of "Naziv mjere" in a row, measure REalizacija is calculated. I have several such a measures, for each colimn on a report (Actual, Actual YTD etc.)

I hope this helps.

Regards,

Zrinko

[Anonymous]

Hi, 

I'm working on a similar problem. I understood your explanation but I can't figure out how you build the final table to display: which are the exact measures/data field that you put in?

Thank you

[zvm]

Hi,

It was a long time ago, I don't remeber exactly. 

You can solve this now using calculation groups.

I believe I created manula table with measuer names. Measeures are normal measuers. This final measure ("Realizacija") calculates all at once.

Regards,