The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hello again,
This is a new version of my previous post. I have this table:
I have to allocate total amount of each job following the rule 30% of total for doers divided by total doers and 70% of total for handlers divided by total handlers.
The new request is that I have a second view of this table totalizing this allocation per names in order to know total amount calculated, as follows:
note my total is a metric (CrossLiquido). Any further explanatios, please let me know.
Thanks in advance
Solved! Go to Solution.
Hi @Fcoatis,
Sorry, it seems like I miss the the type column and only calculate the distinct value.(so the total amount not match the original amount)
Modified formula:
Table = SUMMARIZE(Allocation,[Job],[Name],[Group],[Type], "Date",LOOKUPVALUE(fMovement[Date],[Job],[Job]), "Actual Amount",LOOKUPVALUE(fMovement[Amount],[Job],[Job])*SWITCH([Group],"Doer",.3,"Handler",.7)/ COUNTX(FILTER(ALLEXCEPT(Allocation,Allocation[Name]),[Job]=EARLIER([Job])&&[Group]=EARLIER([Group])),[Group]))
Regards,
Xiaoxin Sheng
Hi @Fcoatis,
Please share some sample data to help us research on these formulas.
Based on your screenshots, I found it's a request which similar as 'calculate the original value and display the it at specific group level'.(similar as you post previous)
So I recommend you to take a look below methods to deal with these similar requirement:
1. Use conditionals to control the measure works on different group level.
Total Measure(Row Count) = IF(ISBLANK(MAX('Table'[Date]))=FALSE(), if(COUNT('Table'[Date])=COUNTX(ALL('Table'),[Date]), "All", if(COUNT('Table'[Date])=COUNTX(FILTER(ALL('Table'),[Date].[Year]=MAX([Date].[Year])),[Date]), "Year Level Total", if(COUNT('Table'[Date])=COUNTX(FILTER(ALL('Table'),[Date].[Year]=MAX([Date].[Year])&&[Date].[QuarterNo]=MAX([Date].[QuarterNo])),[Date]), "Quarter Level Total", if(COUNT('Table'[Date])=COUNTX(FILTER(ALL('Table'),[Date].[Year]=MAX([Date].[Year])&&[Date].[QuarterNo]=MAX([Date].[QuarterNo])&&[Date].[MonthNo]=MAX([Date].[MonthNo])),[Date]), "Month Level Total", "Day Level Total") ))))
2. Get current item and use it to find out the child item and calculate on these value.
You can try to use math function to get the current value(date,number) or use lastnonblank function to get the text value(this function not works on column who contains blank records).
Regards,
Xiaoxin Sheg
Thank you once again @v-shex-msft,
Here is a sample data detailing what I need.
https://www.dropbox.com/sh/ziioxdnfts93ahy/AAAc3x_D3BLAiIGixpwOg-lOa?dl=0
Please let me know if you need further details.
Best Regards
Hi @Fcoatis,
It seems like you share the previous data table, I can't find new columns 'Nome','JOB' in sample file.
Regards,
Xiaoxin Sheng
Thats right @v-shex-msft, I translated them to english.
Can you see the model in excel?
Regards.
This link is the model in pbix.
https://www.dropbox.com/s/d3g0m8g2g4uz7c9/Allocation.pbix?dl=0
Tot Allocation = VAR current_Amount = LOOKUPVALUE (fMovement[Amount];[Job]; MAX ( Allocation[Job] ) ) VAR current_Job = LASTNONBLANK ( Allocation[Job]; [Job] ) VAR current_Group = LASTNONBLANK ( Allocation[Group]; [Group] ) RETURN IF ( COUNTROWS ( Allocation) = COUNTROWS ( FILTER ( ALL ( Allocation ); [Job] = current_Job ) ); current_Amount; IF ( current_Group = "Handler"; ,7 * current_Amount / COUNTROWS ( FILTER ( ALL ( Allocation ); [Job] = current_Job && [Group] = "Handler" ) ); ,3 * current_Amount / COUNTROWS ( FILTER ( ALL ( Allocation ); [Job] = current_Job && [Group] = "Doer" ) ) ) )
Ok I got the Allocation working. Now I have to scale up for names. I´m trying to grab by your example. Updated the model in the previous link.
thanks
Hi @Fcoatis,
For your scenario, I think summary table will be suitable for your requirement.
I create a summary table to store these analysed actual user amount, you can use it to create the matrix visual.
Table = SUMMARIZE(Allocation,[Job],[Name],[Group], "Date",LOOKUPVALUE(fMovement[Date],[Job],[Job]), "Actual Amount",LOOKUPVALUE(fMovement[Amount],[Job],[Job])*SWITCH([Group],"Doer",.3,"Handler",.7)/ COUNTX(FILTER(ALLEXCEPT(Allocation,Allocation[Name]),[Job]=EARLIER([Job])&&[Group]=EARLIER([Group])),[Group]))
Comment:
LOOKUPVALUE(fMovement[Date],[Job],[Job]) , -look up the job date.
LOOKUPVALUE(fMovement[Amount],[Job],[Job]) - job amount
SWITCH([Group],"Doer",.3,"Handler",.7) - percent of group
COUNTX(FILTER(ALLEXCEPT(Allocation,Allocation[Name]),[Job]=EARLIER([Job])&&[Group]=EARLIER([Group])),[Group]) - current group count , used to calculate actual amount of group member.(divide)
Matrix:
Regards,
Xiaoxin Sheng
@v-shex-msftThank you so much for your time,
But something is missing:
Project 2 Total is 120.000
Project 5 Total is 10.000
Project 7 Total is 35.000
If you could check summarize. I´ll be extremely grateful.
Thank you once more
Hi @Fcoatis,
Sorry, it seems like I miss the the type column and only calculate the distinct value.(so the total amount not match the original amount)
Modified formula:
Table = SUMMARIZE(Allocation,[Job],[Name],[Group],[Type], "Date",LOOKUPVALUE(fMovement[Date],[Job],[Job]), "Actual Amount",LOOKUPVALUE(fMovement[Amount],[Job],[Job])*SWITCH([Group],"Doer",.3,"Handler",.7)/ COUNTX(FILTER(ALLEXCEPT(Allocation,Allocation[Name]),[Job]=EARLIER([Job])&&[Group]=EARLIER([Group])),[Group]))
Regards,
Xiaoxin Sheng
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
99 | |
93 | |
82 | |
64 | |
58 |
User | Count |
---|---|
248 | |
123 | |
112 | |
79 | |
78 |