Forum Discussion
cost per user per application
Hi,
I am trying to solve this problem from long time and no solution yet.Can someone pls help me acheive the below results in the ilame from the moddel attached.Pls feel free to change the model if required.I tried many different ways but no correct one yet.I tried to merge Usage,HC but this will be a huge table in my real dataset upto ~8m rows which i think is not required.The problem i see here is there are 3 fact tables(usage,HC,Budget) and not an ideal way to merge them as one.
Pls note - HC table needs to be used for filtering out users who have Flag=0 for a month from the headcount measure.
I am really grateful to those who help me in this.
https://drive.google.com/file/d/1dCpvsmTsNvsJ_PzYMnyEsIcKyeeXMKY6/view?usp=sharing
Anyone having a solution to this,kindly help!!
Expected:
10 Replies
- amitchandak
Super User
Anonymous , see if aggregated tables can help
https://docs.microsoft.com/en-us/power-bi/create-reports/service-aggregates
https://radacad.com/power-bi-aggregation-step-1-create-the-aggregated-table
- vanessafvg
Community Champion
can you explain what the problem is, is it that the data is not adding up correctly, you dont say what the actual issue is with the data provided. what is the condition for the headcount measureHeadcount measure should be distincount of user filtered by HC[Flag]=true
In the model attached,below is the calculation :
HC = CALCULATE(DISTINCTCOUNT(Empl[Employee]),FILTER(RELATEDTABLE(HC),HC[Flag]=TRUE()))The HC measure is not couting correctly if you see the matrix visual in the report.Also,the cost$ measure in model uses sumx which i do not want to use and it slows down performance in my real datasetPls suggest alternative to sumx.- vanessafvg
when i look at your report, it isn't counting jreeves who is set to false, so how is it incorrect, can you give a way to test it?
