Pareto Summary Joining Tables Issue

DAX DAX Calculations
1

Have a working pareto graph which shows sales as a bar and the cumulative % of sales as a line.

Pareto
Pareto925×375 20.3 KB

The following dax measures are used to create this information:
Sales and the product hierarchy key are in the same table.

Dax1:
CY Total Sales = sum(Sales[Invoiced Turnover])

Dax 2:
Pareto Demo PH3 =
VAR TotalRevenue = calculate(sum(Sales[invoiced turnover]),allselected(sales))
VAR CurrentRevenue = Sum(Sales[invoiced turnover])
VAR SummarizedTable =
Summarize(
Allselected(Sales[Product Hierarchy 3]),
Sales[Product Hierarchy 3],
“Revenue”,sum(Sales[invoiced turnover]))
VAR CumulativeSum =
sumx(Filter(SummarizedTable, [Revenue] >=CurrentRevenue),
[Revenue]
)
Return
CumulativeSum/TotalRevenue

When creating the visual, the x-axis is PH3, column y-axis is CY sales, and the line y-axis is Pareto Demo PH3. Everything works fine is as long as the PH key from the sales table is used for the x-axis.
But the PH is only a code and not useful for the visual. What is needed is to show the Key Name instead. The name, however, is referenced in another table (Key + Key Name).

In order for the pareto line to work, the two tables need to be joined to allow using the PH name and/or key. This is where I am stuck.

2

Could you please give more details to allow us to help with this problem, do you have a table already with a unique list of PH Key and PH Name, and does the PH Key match the same data type as the PH Key in the sales table, and does the table contain all the keys in the sales table?

If the above is true, in the model view you should be able to drag the field from one table to the other and create a one to many relationship. With the relationship in place you would need to change the SummarizedTable variable in your measure to use the PH table.

Is there a particular error you are getting when trying to create the relationship?

3

Thank you for the post. I had duplicate keys in the Key + Name Table and this impacted the relationship between the sales and Name + Key table. Removed the duplicates and corrected the relationship and now everything works perfectly. I appreciate the support and the nudge about the relationship.