DAX Percentage to find proportion against a slicer with a date flag set

[DebbieE]

Im really struggling with a bit of DAX at the moment

%  = [Number ]/CALCULATE([Number ],ALLSELECTED('Metrics'))

this is great, obviously until I filter

 

I want to see them all. As an Addition I have a Filter on this visual containing a flag for Previous year against the date table

I tried this DAX 

% by month (against All ) = [Number]/

CALCULATE([Number],

ALL(DimTable))

And this worked  fine without the flag 

But when I add in the flag from the date time table it goes wrong

this is obviously because I dont have anything about month in the DAX.

I have tried a number of things. ALLEXCEPT etc and I just cant figure out how to get this one working

Any help on a solution would be appreciated, I feel like Im almost there

[amitchandak]

@DebbieE ,Can you share sample data and sample output in table format?

[DebbieE]

Unfortunately not. Its a customers report

[amitchandak]

@DebbieE , are you trying % of total and it should remain the same even if you apply filter.

what is the intention of this formula and where it fails functionally?

[DebbieE]

I thought I had included all this in my initial post?

I want the percentage to still be % of total and it should remain the same even if you apply filter WITH the previous month flag

[v-yiruan-msft]

Hi @DebbieE,

It is a little difficult to locate the problem based on the current information. In order to investigate the cause of the problem, could you please provide some simulated data and your expected result? In addition, does the data involved in the report visuals come from a single table or multiple tables? If they come from multiple tables, whether some relationships be created between them? What is the granularity and direction? And the flag "Previous Month" is table field or measure?

Best Regards

Rena

[DebbieE]

Its going to take me a lot of time to get a blank example

However your questions

Does the data involved in the report visuals come from a single table or multiple tables?

Date 

Fact

Dimension

3 tables

If they come from multiple tables, whether some relationships be created between them?

All joined to the fact table. 

What is the granularity and direction?

Singular direction using the keys. All 1 to Many

And the flag "Previous Month" is table field or measure?

Previous Month is a field created with M in the date table

the expected result is in the original visuals I added

With the slicer set AND the flag in filters I still want to see the percentage

[v-yiruan-msft]

Hi @DebbieE ,

Please try to update the formula of measure "% by month (against All )" as below and check if it can get the correct value:

% by month (against All ) =
[Number]
    / CALCULATE (
        [Number],
        FILTER ( ALL ( DimTable ), 'DimTable'[Previous Month] = "True" )
    )

Best Regards

Rena

[DebbieE]

its not worked 

% by month (against All ) =
[Number]
    / CALCULATE (
        [Number],
        FILTER ( ALL ( DimTable ), 'DimTable'[Previous Month] = "True" )
    )

the Previous Month Flag is in the Date table

My Slicer is in the DimTable 

% by month (against All ) = [Number]/

CALCULATE([Number],

FILTER( ALL('Date'),'Date'[Previous Month Flag] = "True"))

I tried this (But now its not taking the slicer into account?

And I get this error

[tex628]

Change the reference from "true" to TRUE(). Looks like your column has Boolean datatype. 

/ J

[DebbieE]

of course, Its boolean so thank you I reset that

Now I get this

this is the new measure that was suggested and it shows 100% against every implicit filter

This is because this filter is now now in the DAX only date is so I didnt think it would work.

can I amend to include both? the Slicer and the Current DaTE FLAG

[DebbieE · Accepted solution]

SORTED

%  month (against All ) = [Number]/

CALCULATE([Number],

FILTER( ALL('Date'),'Date'[Previous Month Flag] = TRUE()),

ALL(DimTable))

I think I even understand how this works

Thanks all

[tex628]

Try this,

% by month (against All ) =
[Number]
    / CALCULATE (
        [Number],
        ALL ( DimTable ), 
        'Date'[Previous Month] = "True" )
    )

For some reason the ALL() Statement is cancelling out the filter on the Date table. Let me know if this works. 

/ J

[tex628]

Do you have a relationship with crossfilter direction "both" between the date table and the main table? 

Br,
J