row level calculation in Matrix visual based on ITEM IDs

[amty63]

Hello,

Below is the screenshot of the report... Requirement is to build the same report in matrix visual.

I have three measures (Actual, V2, Budget) in the fact table.. 

Row Hierarchy needs to create .. I  guess we could create in a static table.. Problem is the calculation :

Here is the static table.

Static table

Now we have to divide every row with the worked days ( ITEM_ID=410 ).. How do we calculate row wise...

[AllisonKennedy]

What are the formulas you are currently using for all three measures and how is the static table generated? It would be useful to see a sample of what the fact table looks like.

Generally to achieve row context in a measure we will use ITERATORS (such as SUMX, MAXX)

Has this post solved your problem? Please mark it as a solution for others to find you quickly and so the community knows that your problem has been resolved.

If you have found this post useful, please give Kudos.

I work as an instructor and consultant for Microsoft 365, specializing in Power BI and Power Query.

https://sites.google.com/site/allisonkennedycv

[AllisonKennedy]

What are the formulas you're currently using for the three measures and how is the static table generated? It would be useful to see a sample of what the fact table looks like.

Generally to acheive row context in a measure we will use ITERATORS (such as SUMX, MAXX)

Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 

If you found this post helpful, please give Kudos.

I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 

https://sites.google.com/site/allisonkennedycv

[amty63]

Normal Sum of Amount for Actual & Budget... granurality of fact tables based on Date & ITEM IDs..

I created Static table manually  in query editor..

I agree ITERATORS works to achieve row context...

If you see screenshot, my main problem is to calculate like 520/410 , 550/410... if we have item_id=410 on  same granularity level...

[V-lianl-msft]

Hi @amty63 ,

Sorry, I can't quite understand your needs.

See if it is help to you:https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/ 

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

[amty63]

I have gone through below link:

https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/

My problem is to I don't have a value for each level in the hierarchy.. 

I just have a values (amount) based on the ITEM IDs only.. Now How to create the hierarchy in the static table to calculate like this..

[AllisonKennedy]

Sorry, I'm still not sure what you're asking. The post you have shared is for if the totals are already in your FACT table, but sounds to me like you have a standard fact table related to date table??

I have looked at the right side of your screenshot, but without more info I still don't quite know what you're trying to do, sorry, just trying to catch up to speed to where you're at. Why do you need the Static table? Are you able to post a redacted preview of the FACT table and another with the actual results you want to achieve (do you want the PIDs listed as you have done on the right or are you trying to sum and divide to get variance??)

Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 

If you found this post helpful, please give Kudos.

I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 

https://sites.google.com/site/allisonkennedycv

[amty63]

Could we have a call on Teams or WEBEX

My problem is to calculate formula for rows where we have ITEM IDs and I have to divide with specific rows with ITEM IDs . Kindly see the right side of the excel screenshot ..