DAx Support on cartesian table

[Daniel___]

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 allocation to profit centers D_Allocation (done by a automated tool), and what I would like to create is a table with allocation and gave the details from the cost center (such, which kind of expenses: as travel, external support costs and so)
Can not attach the Excel/powerbi document but have the data as below (already with the conection created and D_* data tables and MD_* lookup table).

What I was trying was using virtual table to do this calculation, with all variables combination (as a cartesian table).
From the D_Allocation table I just want to add the Account from D_CC_Costs table so then the calculations can be done (in my real scenario this cartesian/Merge table could go easily to hundred thousand million of rows)
Then, my think was with Calculate( Sumx(generate(Table,generate(Table,...)),..) Allexcept), I would be able to know how much the total of the Sender CC (regardless of the type of expenses from table D_CC_Costs) I´m receiving, so then from these would be just multiply the received CC Sender amount (from D_Allocation table ) / Total CC from D_CC_Costs (get the % of allocation) multiplying by the overall amount from the expenses (and all would work out).
My issue in above scenario is allexcept on this new cartesian table do not work as I expect (have the same amount over the rows per sender CC - as I was expecting to behave), but present the overall costs no matter the CC (or other number).
Can you help me with a way to make it work (with the way I was doing or a different way)?

D_Allocation table:

D_CC_Costs Table:

Let me know if I was not clear to express myself,

Thank you in advance

[v-lgarikapat]

Hi @Daniel___ ,

Thanks for reaching out to the Microsoft fabric community forum.

@lbendlin 

Thanks for your prompt response.

@Daniel___ 

Could you please provide sample data that fully represents the issue or question you're referring to? Kindly ensure the data is in a usable format (e.g., Excel or CSV) rather than a screenshot, and does not contain any sensitive or unrelated information.

Looking forward to your response.

Best regards,
Lakshmi.

[v-lgarikapat]

Hi @Daniel___ ,

We haven’t heard back from you regarding our last response, so I just wanted to check in to see if you were able to resolve the issue. If you have any further questions or need assistance, please feel free to let us know.

Best Regards,

Lakshmi

[v-lgarikapat]

Hi @Daniel___ ,

We haven’t heard back from you regarding our last response, so I just wanted to check in to see if you were able to resolve the issue. If you have any further questions or need assistance, please feel free to let us know.

Best Regards,

Lakshmi

[v-lgarikapat]

Hi @Daniel___ ,

We haven’t heard back from you regarding our last response, so I just wanted to check in to see if you were able to resolve the issue. If you have any further questions or need assistance, please feel free to let us know.

Best Regards,

Lakshmi

[lbendlin]

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-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[Daniel___]

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=10..."

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]

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___]

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

[v-lgarikapat]

Hi @Daniel___ ,

Thanks for your prompt response

Your CC1 measure is a straightforward SUMX over the D_CC_Costs table's Actual YTD column, which works well whether applied directly or through the cartesian table of MD_* combinations. The challenge with CC2 arises when trying to subtotal by sender cost center using CALCULATE + SUMX + ALLEXCEPT, especially when the measure is applied via the cartesian table and the filter context doesn’t flow properly. To resolve this, you can use SUMX(VALUES(D_CC_Costs_Sender[CC_]), CALCULATE(SUM(D_CC_Costs[Actual YTD]))) to iterate over distinct sender cost centers, or apply KEEPFILTERS(VALUES(...)) to preserve granularity. If the MD_CC_Costs_Sender table is disconnected, TREATAS(VALUES(MD_CC_Costs_Sender[CC_]), D_CC_Costs_Sender[CC_]) can simulate the relationship and ensure the filter reaches the fact table. These approaches help maintain correct subtotaling and grouping logic across both direct and cartesian contexts.

We appreciate your engagement and thank you for being an active part of the community.

Best Regards,

Lakshmi.

[Daniel___]

Hi v-lgarikapat,

Did try the options which you mention ad they did not generate the outcome which I was looking for, repeat the same number over the rows as subtotal per Sender CC (in a cartesian table in my model all conbinations of the tables Start by "MD*").

would be possible to sugest other option?

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

Thank you in advance.

[v-lgarikapat]

Hi @Daniel___ , Thank you for your responses. Could you please share a sample PBIX file that does not contain any sensitive or unrelated information? Having a reference would be extremely helpful.

We appreciate your engagement and thank you for being an active part of the community.

Best Regards,

Lakshmi.

[Daniel___]

Hi v-lgarikapat,

On the below link we can find the Link with the excel file, if we do the download and open it, then we can find t full model example (all data good to be shared and used).

 "https://docs.google.com/spreadsheets/d/1fC8M8WFmWoRzIUx-1LQVbSrIEem-Ykro/edit?usp=drive_link&ouid=10...".

Thank you in advance,