Forum Discussion

wvadik's avatar
wvadik
Helper III
6 years ago
Solved

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 ...
Need Help
  • mwegener's avatar
    mwegener
    6 years ago

    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.

  • mwegener's avatar
    mwegener
    6 years ago

    Hi wvadik ,

     

    you have to expand the hierachy on the axis.

    Currently blank is returned if there is no filter on the Measure "Dimension".

    If I answered your question, please mark my post as solution, this will also help others.

    Please give Kudos for support.

mwegener's avatar
mwegener
Most Valuable Professional
6 years ago

Hi wvadik ,

 

I don't understand your data model in detail, but a data model with100 measure columns don't sound healthy to me.

Do you really need to precalculate all of these measures in your fact table?

Or could these calculations also be based on movement data?

 

mwegener's avatar
mwegener
Most Valuable Professional
6 years ago

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.