How i can define a Dax to summarize True resultant values from another DAX in a Matrix?

[arlequin71]

Hello, i´m looking for a Dax to summarize the highlighted Totals in the attached Matrix image (by Region AN).

1 Value is result of the following DAX (AN Region's example):

AN by Category =

Var ANinCat = CALCULATE(DISTINCTCOUNT(dim_country[Region]),FILTER(dim_Country,dim_country[Region]="AN"),FILTER(ft_Sales,ft_sales[Sales]<>BLANK()))

Var CatinAN =

CALCULATE(COUNTROWS(DISTINCT(dim_customers[Category])),

FILTER(dim_Country,dim_country[Region]="AN"),FILTER(ft_Sales,ft_Sales[Sales]<>BLANK()),ALLEXCEPT(dim_Country,dim_country[Region]), ALLEXCEPT(ft_Sales,ft_Sales[Sales]))

RETURN

IF(ISINSCOPE(dim_customers[Category]),ANinCat,CatinAN)

Ctgry in AN =

CALCULATE(COUNTROWS(DISTINCT(dim_customers[Category])),

FILTER(dim_Country,dim_country[Region]="AN"),FILTER(ft_sales,ft_sales[Sales]<>BLANK()))

The second part of IsInscope is the one i need to fix (var CatinAn) in order to reflect the right total value.  '2' in this case.

These are the scripts to recreate the example's tables:

dim_customers

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwMFDSUXJOLElNzy+qVHBUitUBixoiizrBRI2QRZ1hosbIoi4wURNkUVel2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Customer_ID = _t, Category = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer_ID", Int64.Type}, {"Category", type text}})
in
#"Changed Type"

ft_sales

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc6xDcAwCATAXahdPBgnziyW918jQSjhixTI6PSA1xKVlgXgeUwhuyWP5Gg7iintxEZLiI9aYqP4TI6hz2Zaj3uzohex4S+tlPZkf7+xbw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ProductID = _t, CountryId = _t, Customer_ID = _t, Sales = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"CountryId", Int64.Type}, {"Customer_ID", Int64.Type}, {"Sales", Int64.Type}})
in
#"Changed Type"

dim_country

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXLOyMxJBdLB/s7+SrE60UpGQI5jUXpqXklmXiKyhDGQE1pUml6aWIksbALk+KZWZCbngxgRYDFTIDMgsSgRqtbRDyxqBmQ65edklmUmIgTNgUzX5NLElPwihKAFyGH5Ofm5SchKLUGmphaVQkViAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Country ID" = _t, Country = _t, Region = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Country ID", Int64.Type}, {"Country", type text}, {"Region", type text}})
in
#"Changed Type"

dim_product

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIEYUel2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ProductID = _t, Division = _t, BusinessUnit = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ProductID", Int64.Type}, {"Division", type text}, {"BusinessUnit", type text}})
in
#"Changed Type"

Datamodel

I really appreciate any help.  Regards.

[v-yangliu-msft · Accepted solution]

Hi  @arlequin71 ,

This function is used to modify Total.

Ctgry in AN_Hasonevalue =
var _table=SUMMARIZE('dim_customers',[Category],"1",[Ctgry in AN])
return
IF(HASONEVALUE(dim_customers[Category]),[Ctgry in AN],SUMX(_table,[1]))

Create a virtual table by SUMMARIZE, and judge by HASONEVALUE. If there is a value, it will display [Ctgry in AN], and if there is no value, it will display the correct Total.

For example, I should display Total as 4 here, but the display is all 1:

After using the function, Total will display the correct value:

Incorrect Total is a common problem, this article explains why and how to solve it:

https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

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.

[v-yangliu-msft]

Hi  @arlequin71 ,

You can use hasonevaluation to display the value in total.

Here are the steps you can follow：

1. Create measure.

Ctgry in AN_Hasonevalue =
var _table=SUMMARIZE('dim_customers',[Category],"1",[Ctgry in AN])
return
IF(HASONEVALUE(dim_customers[Category]),[Ctgry in AN],SUMX(_table,[1]))

2. 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.

[arlequin71]

Hi Liu, thanks for your answer, your approach is closer to the solution i require but what specifically need is to fix the first column Total.  In my example it is showing 4 instead 2 which is the right result.

Thanks for your help.