cost per user per application

[Anonymous]

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:

[Anonymous]

Any replies and help on this please??

[vanessafvg]

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 measure

[Anonymous]

Headcount 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 dataset

Pls suggest alternative to sumx.

[vanessafvg]

can you also explain the purpose of what cost is trying to do? divide the unit cost into the users?

[Anonymous]

Here is the requirement:

There are 3 products in the Budget table: Salesforce, ORacle, and Telecom. Each of them has a $ monthly allocation in the budget table. I divide the monthly allocation into the $ cost per user based on the HC measure. The HC measure must calculate the count of users per product filtered by HC [Flag] =1.Monthly alocation per product divided by this HC will give unit cost. This unit cost will be used to calculate the costs rolled up to fields in the Empl and Date dimensions.

[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?

[Anonymous]

From the matrix,you see that HC counts pjohn as 1 (for Oracle)which shouldnt be the case as Usage table doesnt have pjohn using Oracle. Second,the cost$ is wrong as well because it counts ORacle as 2 users instead of 1 for June and divided cost by 2.Actually,i should get the below output :

[v-diye-msft]

Hi @Anonymous 

Not quite undertsand what you're looking for, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490 

You might consider providing your dummy pbix that would be helpful for us to investigate it further. 

You can upload it to the onedrive for business and share the link here. please don't forget to disclose the expected results and remove the confidential info.

[Anonymous]

Hi,

Thanks for your reply.I have provided dummy pbix in my original post.I once again post it here :

https://drive.google.com/file/d/1dCpvsmTsNvsJ_PzYMnyEsIcKyeeXMKY6/view?usp=sharing

[amitchandak]

@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