Forum Discussion

BIlix's avatar
BIlix
Icon for Helper II rankHelper II
3 years ago

Need Help for a Dax Visual

Hello Community,

I have a problem with a Dax Measure.

I want to calculate the Costs of a Project.
The costs of the Project have a specified Formula.

First I will breakdown some Data.

I have a Dimension Table "Sprint" Which includes the Sprint Number, The Hourly Costs  and the Total Number of Working Hours for THis SPrint. (Hourly Costs are same over each sprint)

In a Fact Table I have the regarding Sprint Number and Story Points which were finished during the the Sprint. THere is also a ProjectID for every entry in the Fact Table

There is a dimension Table which has a column with a scaling factor for these Story Points. THese are adjusted Story Points

E.G. 5 Story Point TIckets always gets multiplied by 1,4, 10 Story Point Tickets always by 1,7 and so on.

The costs should be displayed in Matrix Visual for every Sprint and the Projects in the regarding Sprint.

The formula for the cost of a Project in Lets say Sprint Number 6  is (Hourly Costs of the Sprint * Total Adjusted Story Points for the Project during the regarding Sprint) / Total Adjusted Story Points for the whole Sprint and then multiplied by the Total Number of Working Hours during the whole Sprint.

I already have a measure for the Adjusted Story Points: 
SUMX(FactTable, Lookupvalue(DimSP[Scaling Factor], DimSP[SPID], FactTable[SPID) * FactTable[SP])

The results here are fine. But the future calculations seem to be wrong.

2 Replies

  • BIlix's avatar
    BIlix
    Icon for Helper II rankHelper II
    3 years ago

    Anyone could help? Or do you maybe need additional information?

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi BIlix ,

     

    Not sure, please try:

    ProjectCosts =
VAR HourlyCosts = SELECTEDVALUE(Sprint[HourlyCosts])
VAR TotalWorkingHours = SELECTEDVALUE(Sprint[TotalWorkingHours])
VAR TotalAdjustedSP = SUMX(FactTable, LOOKUPVALUE(DimSP[Scaling Factor], DimSP[SPID], FactTable[SPID]) * FactTable[SP])
VAR TotalAdjustedSPAllProjects = CALCULATE(TotalAdjustedSP, ALL(FactTable[ProjectID]))
RETURN 
IF(TotalAdjustedSPAllProjects <> 0, (HourlyCosts * TotalAdjustedSP) / TotalAdjustedSPAllProjects * TotalWorkingHours, 0)

    I hope this is helpful to you! If this doesn't work, please let me know. It would be nice to have some sample data and expected output.

    How to provide sample data in the Power BI Forum - Microsoft Fabric Community

     

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum