Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
I have "flattened" my bom structure to one table with columns like this:
I have product table (like item data) for attribute values (like std price) by part:
Now:
I am able to create a hierarchy from partid_level columns - and it is working: when I select mainitem I am able to see the parts under it in matrix visual:
What I need now:
1. way to show qty for each part based on the qty_level columns
2. std price value from my item table for each part
3. measure to calculate qty_level x std_price
Anyone?
Solved! Go to Solution.
@TTS , For That you can have measure like
Sumx(Table, Table[Qty] * Table[Price])
if price is in master(1-m Join)
Sumx(Table, Table[Qty]* realted(Item[Price]) )
Because you have different levels, Use isinscope to switch such levels, Lowest level first
IsInScope - Switch Rank at different levels: https://youtu.be/kh0gezKICEM
Hi users, @amitchandak , @TTS
Great that you got required output.
But I want to know how do modelled the tables there because when you join mainitem (BOM table) with PART (DIM_PARTS), you will get the same price of mainitems for part levels of that mainitem.
Expectation is to get price of part level items in the matrix.
@TTS , For That you can have measure like
Sumx(Table, Table[Qty] * Table[Price])
if price is in master(1-m Join)
Sumx(Table, Table[Qty]* realted(Item[Price]) )
Because you have different levels, Use isinscope to switch such levels, Lowest level first
IsInScope - Switch Rank at different levels: https://youtu.be/kh0gezKICEM
Hi @amitchandak
And great thanks for Your message; I was too close to see it clearly from my data model point of view.
I made few changes and was able to avoid sumx by using hierarchy values and by creating enough measures...and then switch ( true () ... to get correct measure to correct level.
Logic works...now it is time to check values.
Great thanks! 💪
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
112 | |
105 | |
95 | |
58 |
User | Count |
---|---|
174 | |
147 | |
136 | |
102 | |
82 |