Forum Discussion

yanisyes's avatar
yanisyes
Frequent Visitor
4 years ago
Solved

Add data in a clustered column chart without modifying data source

Hello,

 

I've created the column stacked graph below 

 

My measure is just a simple Calcultate(countrow(mytable)), and I've put the sub category as column serie.
WHat I want to do it to modify only the forecast data in February and minus one the column to have the graph below

Is it possible to do that without modifying my source data( with a measure for example).
Because when I try to use a calculate like this Calculate(countrow(mytable)+1,mytable[sub-category]="Forecast") it is modyfying all my data not only the forecast for jan and feb

 

I just want to  modify one specific sub category (here forecast) for one specific month (Here February).

 

THanks in advance for your help.

 

Need Help
  • Arul's avatar
    Arul
    4 years ago

    yanisyes ,

    I hope the below is your expected output, this can be achieved by writing one measure like the below by modifying it based on your data,

     

    Sales Feb = IF(SELECTEDVALUE(financials[Month Name])="February",financials[Sales]+30000000,financials[Sales])

     

    use this in your visual

    Thanks,

    Arul

     

  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi yanisyes ,

    Please refer to my pbix file to see if it helps you. 

    Create a measure.

    Measure =
VAR _month =
    MONTH ( MAX ( 'Table'[date] ) )
VAR _year =
    YEAR ( MAX ( 'Table'[date] ) )
RETURN
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            ALL ( 'Table' ),
            MONTH ( 'Table'[date] ) = _month
                && YEAR ( 'Table'[date] ) = _year
                && 'Table'[sub-category] = SELECTEDVALUE ( 'Table'[sub-category] )
        )
    )

     

    If I have misunderstood your meaning, please provide your pbix file without privacy information and desired output with more details.

     

    Best Regards

    Community Support Team _ Polly

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • Arul's avatar
    Arul
    Super User
    4 years ago

    yanisyes ,

    I hope the below is your expected output, this can be achieved by writing one measure like the below by modifying it based on your data,

     

    Sales Feb = IF(SELECTEDVALUE(financials[Month Name])="February",financials[Sales]+30000000,financials[Sales])

     

    use this in your visual

    Thanks,

    Arul

     

  • yanisyes's avatar
    yanisyes
    Frequent Visitor
    4 years ago

    Hi Arul, 
    First thanks for your answer but all my data are not in column like in your case so I can't do the thing you are suggesting + if I have two measure in the columns values ( means no column serie). I will not be able to click only on forecast or actual or baseline for the month selected.
    WHen I will try to click it will only filter the month with all my data (it will display forecast baseline, actual of February if I click on forecast for the month of february). 
    THis is why I've created only one measure 

    Here is my data

    My measure is counting the number of time the month appear for each subcategory and I want to delete the forecast of the milestone  3 in feb to change my chart from this 

    TO this

     

    THanks.

     

     

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi yanisyes ,

    Please refer to my pbix file to see if it helps you. 

    Create a measure.

    Measure =
VAR _month =
    MONTH ( MAX ( 'Table'[date] ) )
VAR _year =
    YEAR ( MAX ( 'Table'[date] ) )
RETURN
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            ALL ( 'Table' ),
            MONTH ( 'Table'[date] ) = _month
                && YEAR ( 'Table'[date] ) = _year
                && 'Table'[sub-category] = SELECTEDVALUE ( 'Table'[sub-category] )
        )
    )

     

    If I have misunderstood your meaning, please provide your pbix file without privacy information and desired output with more details.

     

    Best Regards

    Community Support Team _ Polly

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.