Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Incorrect Total

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's avatar
    Anonymous
    4 years ago

    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

3 Replies

  • AilleryO's avatar
    AilleryO
    Memorable Member
    4 years ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    AilleryO 

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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