Calculate Sales on related dim_Product table

[arlequin71]

Hello,

Anyone can help me to build a metric to calculate Sales of Prioritized Products to be compare with No Prioritized Productcs?.  These products are country associated.

Thanks in advance for your help.

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


dim_Product
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKTylNLlFwVNJRAmNHpVgdhLATdmFnoJATprALdmFXoBBYh5NSbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product = _t, #"Categoy 1" = _t, #"Category 2" = _t])
in
    Source


Prioritized Products
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKTylNLlFwVNJRCg1WitVBCDljCjkBhRyDUIQcsQs5+2CYhSYEtjESw3gnVLNcoI6IBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product = _t, Country = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product", type text}, {"Country", type text}})
in
    #"Changed Type"


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

[Ashish_Mathur · Accepted solution]

Hi,

Share some data, describe the business context and show the expected result.

[v-jingzhang · Accepted solution]

Hi @arlequin71 

I add a table into your model and modify table relationships as below.

In the new table, Is Prioritized Product column is used to tell whether a product is a prioritized product in a country.

I display the sales in a matrix visual like below. You can download the attachment for details.

Kindly let me know if this helps.
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.

[Ashish_Mathur · Accepted solution]

Hi,

Share some data, describe the business context and show the expected result.