Forum Discussion

Daniel___'s avatar
Daniel___
Frequent Visitor
1 year ago
Solved

DAx Support on cartesian table

Hello all,   Would like to get you support for a Dax formula. Teorical concept: there are two tables with data one Cost centers (D_CC_Costs) with Expenses and other table the final oucome of alloca...
  • Daniel___'s avatar
    Daniel___
    11 months ago

    Hi @lbendlin , Hi @v-lgarikapat ,

     

    Thank you both for the time/patience and the support. I was able to figure out the way :).

     

    So, Calculate + sumx + all is the key to be able to repeat over the rows te same amount in a cartesian table.

     

     

    Thank you

lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
1 year ago

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

  • Daniel___'s avatar
    Daniel___
    Frequent Visitor
    1 year ago

    Hi Ibendlin,

     

    Sorry on the time which I got to came back 😞 (on something wihch I need support my fault).

     

    Let me try to explain what I´m trying to do in a different way.

    I have 2 tables with data and 5 tables with unique data to do the connection between the data tables.

     

    Then, Need to create a cartesian table as attach file Pivot Table 4 (just the combination of the 5 unique tables). There I try to Calculate Measure CC2 and Measure PC2 and they are the total Per Sender CC in Both data tables Pivot Table 3 and Pivot Table 5 (the "Should be" in green Column 7/8 in Pivot table 4).

    Then having it, will be abe to do the calculations of 9/10/11/12 (being 12 the end goal and having this would have the allocation detail which I´m looking for :)).

     

    Link of Excel file: "https://docs.google.com/spreadsheets/d/1fC8M8WFmWoRzIUx-1LQVbSrIEem-Ykro/edit?usp=drive_link&ouid=100880094106413413304&rtpof=true&sd=true"

     

    If I was not able to explain myself in a good way just let me know.

    Thank you in advance for the time and support.

     

    • lbendlin's avatar
      lbendlin
      Icon for Super User rankSuper User
      1 year ago

      Thank you for the sample data.  I's a bit inconclusive. Not clear to me how measure CC1 is calculated?  Is it based on Sum of Actual?

      • Daniel___'s avatar
        Daniel___
        Frequent Visitor
        1 year ago

        Hi Ibendlin,

         

        The Measure CC1 is a simple Sumx from D_CC_Costs Table Column "Actual YTD". And this portion of formula is working well - either we apply the Measure via D_CC_Cost Table or via "cartesian table" (cartesian table being,  add the all the combinations of the conection tables - Tables start by "MD*").

        The Measure CC2 is where I´m strugling, so basically would be: Calculate + sumx + allexcept (same parameters as above with the filter being applying on D_CC_Cost_Sender CC_ and ideally in MD_CC_Costs_Sender CC_). The measure works well when we apply the measure via D_CC_Cost Table and are not able to identify the cost center and do the subtotal in the "cartesian table".

         

        So here is where I would need your support advice,

         

        Let me know if I was able to explain myself in a good way,

         

        Thank you in advance