Forum Discussion

maddypena07's avatar
maddypena07
Frequent Visitor
2 years ago
Solved

Nested FIXED LOD (tableau) to dax

Hello all! My company is migrating to powerBI from Tableau and I've been trying to replicate some calculations. There is a fair amount of calculations that use LOD due to the level of detail of the databases and figures that can only be joined at a certail level. One of them is to calculate the Uptime of a machine first by the manchine itself and the weeks selected and then the sum of those values at week level, like this:

So my expected results are, for the inner fixed: 

Whis this calc:

CALCULATE(
    sum('table'[Uptime (hrs)]),
    ALLEXCEPT('table', 'table'[Machine],'table'[ISO Week]))
 

 

I'm able to replicate the inner fixed, but I'm unable to replicate the outer Fixed that takes the previous result and stablish it as a result for each machine.

I really appreciate your time and help!
 
Edit:
Dummy data and .pbix file
Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi maddypena07 ,

    I updated your sample pbix file(see the attachment), please check if that is what you want. You can create a measure as below to get it:

    Sum of Uptime by Week = SUMX ( ALLEXCEPT ( 'dummt', 'dummt'[ISO Week] ), [Uptime (hrs)] )

    Best Regards

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    2 years ago

    maddypena07 I don't really speak Tableau so much of the jargon is going over my head. What is it that you are having an issue with spefically. As in, which of the numbers you are trying to create is wrong? Everything looks good in your 2 images.

    • maddypena07's avatar
      maddypena07
      Frequent Visitor
      2 years ago

      Thanks Greg!

      I apologize for the confussion!. The final result of the tableau calculation (first image) should be:

      the value 4883 on every row. I separated both FIXED results to try to resolve it steb by step.

       

      I can get to the first part of the calculation (Inner fixed), the 

      {fixed [Weeks],[MachineID]:
      sum([Uptime (hrs.)])}
      And I get the results as the last image, but I don't know how to get that final result as a value for every row on the table.

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi maddypena07 ,

        I updated your sample pbix file(see the attachment), please check if that is what you want. You can create a measure as below to get it:

        Sum of Uptime by Week = SUMX ( ALLEXCEPT ( 'dummt', 'dummt'[ISO Week] ), [Uptime (hrs)] )

        Best Regards