Calculated Columns Model

[Anonymous]

Hi,

I have a model that is currently on excel and wish to import it on power bi given its much easier to analyse.

What i m after at is having 3 tables - Rules table -> Table 1 , Source Data Table -> Table 3 and my Calculations table -> Table 2. 

What i'm after at getting data from the source data and the rules data based according to the unique username so I guess this needs to be done using calculated columns. For ease of reference i put a screenshot of the tables i m mentioning above. 

Could you help me out as I am relatively new to power BI.

[Icey]

Hi @Anonymous ,

Kindly check if the attchedments are helpful.

Best Regards,

Icey

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

[FrankAT]

Hi @Anonymous 

you can do it super easy with Power Query like this:

// RulesTable
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Xc65CcAwEATAXg6cKbjVr9BuQ6j/NrRgsBeFk82cdj/usGDwy1Z4HekoTnQSZzqLC13Ela7iRjdxp7t40OM3nBYePRw9fL21AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Username = _t, #" Discount" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Username", type text}, {" Discount", Percentage.Type}})
in
    #"Changed Type"

// SourceTable
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Xc0xDsAgDEPRu2RmiBOg7dheA3H/a5CqUmUy+g3+Y8j9qEKKNFWVWT6wgM7gAZWhBjjtFtto99hoBMf7wHDm6JWi0BQF9ihsj8L/y7kA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Username = _t, #" Sales" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Username", type text}, {" Sales", Int64.Type}})
in
    #"Changed Type"

// CalculationTabel
let
    Source = Table.NestedJoin(RulesTable, {"Username"}, SourceTable, {"Username"}, "Calculation Table", JoinKind.LeftOuter),
    #"Expanded Calculation Table" = Table.ExpandTableColumn(Source, "Calculation Table", {" Sales"}, {" Sales"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Calculation Table", "Sale post Discount", each [#" Sales"] - [#" Sales"] * [#" Discount"], type number)
in
    #"Added Custom"

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)

[Anonymous]

Could you take me through what you did wuth power query pls?

[Greg_Deckler]

@Anonymous - Seems to me that you should use a Merge query to merge tables 2 and table 3. Then you could create a calculated column for your total price discount.

If you want to keep them separate, you could form a relationship between the two tables. Then you could put Username from one of the tables in a table visualization and then put Discount and Sales in that same visualization and create a measure like:

Measure = MAX('Table1'[Discount]) * MAX('Table3'[Sales])

and put that into your visualization.

If you want an actual Table 2 in your data model created via DAX you could do that as well. Using NATURALINNERJOIN or ADDCOLUMNS, etc.

Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.