Incorrect Total

[Anonymous]

Hi Guys,

I'm having incorrect Rankx Total on my measure. see screen shot &  DAX query below, When i filter on fiscal Year, The RankTotal changes to 13 which is incorrect instead of 12. 

Table Rank2 =

VAR _DivisonRanking =

IF (

NOT ISBLANK( [Percent] ),

CALCULATE (

RANKX (

FILTER ( ALL ( dim_sites[Division] ), NOT ISBLANK( [Percent] ) ),

[Percent],

,

DESC,

DENSE

),

ALL ( dim_sites[Region] ),

ALL ( dim_sites[Site_District] ),

ALL ( dim_sites[Site_Name] )

)

)

VAR _RegionRanking =

IF (

NOT ( ISBLANK( [Percent] ) ),

CALCULATE (

RANKX (

FILTER ( ALL ( dim_sites[Region] ), NOT ( ISBLANK( [Percent] ) ) ),

[Percent] ,

,

DESC,

DENSE

),

ALL ( dim_sites[Site_District] ),

ALL ( dim_sites[Site_Name] ),

ALLSELECTED ( dim_sites[Division] )

)

)

VAR _MarketRanking =

IF (

NOT ( ISBLANK( [Percent] ) ),

CALCULATE (

RANKX (

FILTER ( ALL ( dim_sites[Site_District] ), NOT ( ISBLANK( [Percent] ) ) ),

[Percent],

,

DESC,

DENSE

),

ALL ( dim_sites[Site_Name] ),

ALLSELECTED ( dim_sites[Region] ),

ALLSELECTED ( dim_sites[Division] )

)

)

VAR _SiteRanking =

IF (

NOT ( ISBLANK( [Percent] ) ),

CALCULATE (

RANKX (

FILTER ( ALL ( dim_sites[Site_Name] ), NOT ( ISBLANK( [Percent] ) ) ),

[Percent],

,

DESC,

DENSE

),

ALLSELECTED ( dim_sites[Region] ),

ALLSELECTED ( dim_sites[Site_District] ),

ALLSELECTED ( dim_sites[Division] )

)

)

VAR _Results =

SWITCH (

TRUE (),

HASONEFILTER ( dim_sites[Site_Name] ), _SiteRanking,

HASONEFILTER ( dim_sites[Site_District] ), _MarketRanking,

HASONEFILTER ( dim_sites[Region] ), _RegionRanking,

HASONEFILTER ( dim_sites[Division] ), _DivisonRanking,

BLANK ()

)

RETURN

_Results

[Anonymous · Accepted solution]

Hi  @Anonymous ,

You can use the HASONEVALUE() function to fix the Total incorrect

Here are the steps you can follow：

Create a measure.

Total_Incorrect =
var _table=SUMMARIZE('dim_sites','dim_sites'[Division],"_value",[Table Rank2])
return
IF(HASONEVALUE('dim_sites'[Division]),[Table Rank2],SUMX(_table,[_value]))

Result

Best Regards,

Liu Yang

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

[Anonymous]

@AilleryO 

Look at the DAX correctly let me know if i need to include the date dim.. or which filter context I'm missing.

[AilleryO]

Hi,

Do you have any blank value hidden ?

It must be related to context filters, because as soon as you are on a total (=no filter) or in a visual card (= no filter) you get 13 instead of 12. It means that your ranking without filters is incorrect.

Try to look for missing filters in your results...

Hope it helps a bit