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 think we understood you correctly.

 

You may download my PBIX file from here.
Hope this helps.

 

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

Please give Kudos for support.

wvadik's avatar
wvadik
Helper III
6 years ago

mwegener, Thanks. Greate work.

This is very close to the result, but my stacked column chart should have two dimensions on the axis in addition to the day numbers. If I add one more dimension to the "Axis" (Field1 in your example) in addition to the SortID field already there (and in my case, I need to add two more measurements to the "Axis"), then the stacked column chart shows emptiness. I suspect this is because the calculated MeasureTable table is not dimensionally related, but I could be wrong. I attach your example with the added field to the "Axis". 

  • mwegener's avatar
    mwegener
    Most Valuable Professional
    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.

  • wvadik's avatar
    wvadik
    Helper III
    6 years ago

    mwegener, Thank you VERY VEDRY match 🙂

    It's greate!