Slicers not working well in measure needed for stacked column chart

[DM_BI]

Hello, 

I had a first problem with my column charts (here) but thanks to Maggie's help I could resolve it. However with the new measure using ALLEXCEPT, I can't filter by brand anymore... (please note that in my first problem I hadn't precised that in my real dashboard I had to filter by brand so my mistake was not to have said it from the beginning).

The first measure was :

FACTURATION running total in MONTH =

CALCULATE(

    SUM('Hoja1'[FACTURATION]);

    FILTER(

        ALLSELECTED('Hoja1');

        ISONORAFTER('Hoja1'[MONTH]; MAX('Hoja1'[MONTH]); DESC)

    )

)

The new measure is : 

(2) FACTURATION running total in MONTH =

CALCULATE(

    SUM('Hoja1'[FACTURATION]);

    FILTER(

        ALLEXCEPT('Hoja1';Hoja1[Convoc]);

        ISONORAFTER('Hoja1'[MONTH]; MAX('Hoja1'[MONTH]); DESC)

    ))

Does anyone know how I can show both :

- the right result in the stacked column charts

- the chart for a specific brand

Thank you very much for your help, I've been thinking on it too much and I think it's a problem with ALLstuffs but I don't understand exactly...

DM

the sample file : https://wetransfer.com/downloads/9a4b68ca785b7104b71ebcbd2779fea720190125160443/a5b38efee6bec9666bf7...

[Anonymous]

Hi @DM_BI,

AFAIK, current power bi not support to create a dynamic calculate column/table based on slicer/filter.

I'd like to suggest you add more conditions to current category group(convoc) and rolling range(month), so you formula can calculate more correctly.

In addition, can you please upload your sample to onedrive or google drive and share link here?

Regards,

Xiaoxin Sheng

[DM_BI]

Hi Xiaoxin,

Thank you for your answer. It's not a calculated column or table, it's a calculated measure, does it change anything? Also I can't share the real pbix file because it's confidential 😞 Do you see what I mean in the sample file in the wetransfer link?

I need to show on the graph the running total of sales by months and be able to filter by month and brans.

Could you please explain me what do you mean by "add more conditions to current category group(convoc) and rolling range(month), so you formula can calculate more correctly." ? If I understand, I have to add a filter in the formula and then create differente measures?

Thank you,

[Anonymous · Accepted solution]

Hi @DM_BI,

Actually, I can't view you sample from link you shared.(maybe due to my policy setting?) When you calculate rolling total with slicer on same table, it will broken looping calculation.

For this scenario, I'd like to suggest you create a calendar table with relationship to current table, use calendar date as axis and measure calculate through whole fact table with current calendar date as condition.

FACTURATION running total in MONTH =
VAR currDate =
    MAX ( Calendar[Date] )
RETURN
    CALCULATE (
        SUM ( 'Hoja1'[FACTURATION] );
        FILTER ( ALLSELECTED ( 'Hoja1' ); 'Hoja1'[MONTH] <= currDate );
        VALUES ( 'Hoja1'[Convoc] )
    )

Cumulative Total

Regards,

Xiaoxin Sheng

[DM_BI]

Hello,

I finally understood my mistake. For the running total I had used the months of my fact table instead of my dimension table...

Thank you for having tried to help me!

DM

[DM_BI]

Anyone?