Forum Discussion
Stacked Column Chart like Matrix
Hi guys,
I have table facts with some data and next fields:
Date, Field1....5 (dimension fields), Day1, Day2, Day3.... etc (measure fields - count some facts that happened since the Date on the first day, second day, third day etc).
In my report I need Matrix and Stacked Column Chart. With the matrix, everything worked out.
On the Stacked Column Chart I need something similar: each column should display one day (mesures Day1, Day2 ... etc) on Axis.
I know that the source data should be in a different format for this chart:
Date, Field1....5, DayNumber, DayQnt (measure field)
But the source data is large enough, and if I transform it into this format, then the size of the fact table will be about 4 billion rows.
Therefore, I am looking for a way to solve my problem using visualization in the report.
How to create the one I need Stacked Column Chart ?
Hi wvadik,
here is an example for the first 10 days.
Calculated Table:
MeasureTable = SELECTCOLUMNS(GENERATESERIES ( 1, 100 ,1 ), "Measure", "Day" & [Value], "Sort ID", [Value])Measure:
MeasureSwitch = IF ( HASONEVALUE ( MeasureTable[Measure] ), SWITCH ( SELECTEDVALUE ( MeasureTable[Measure] ), "Day1", SUM ( facts[Day1] ), "Day2", SUM ( facts[Day2] ), "Day3", SUM ( facts[Day3] ), "Day4", SUM ( facts[Day4] ), "Day5", SUM ( facts[Day5] ), "Day6", SUM ( facts[Day6] ), "Day7", SUM ( facts[Day7] ), "Day8", SUM ( facts[Day8] ), "Day9", SUM ( facts[Day9] ), "Day10", SUM ( facts[Day10] ), BLANK (), BLANK () ) )If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.
10 Replies
Hi wvadik ,
look at this. "Less Columns, More Rows = More Speed!"
https://powerpivotpro.com/2011/08/less-columns-more-rows-more-speed/
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.
Thanks mwegener
I have a fact table with data for the last two years. In it, the data are calculated by day. For each day, calculations were made 100 days in advance. In total, in my fact table, there is one column with a start_date + 23 dimension columns that form groups and 100 measure columns (the sum for the group for each day is from 0 to 100 from the starting date of the group). All this in compressed form occupies 45 million rows. If I make an univot of 101 measure columns of days to turn into a table of the form: start_date + 23 measure columns + Day Number + Measure column for a specific day. That in this case, the size of the fact table that will need to be processed into a cube in the SSAS will be equal to 45 million * 101 = 4.5 billion rows. I think that the processing of such a table will be very long and will take a very long time.
