Cross Table to Flat Table

[fklatecki]

Greetings,

Tables:

"rebates"

"sales"

Calculated columns:

Rebate 2016B = IF(sales[year]=2016, sales[Rebatable]*related('2016 Rebate'[Percent%]))

Adj 2016 Rebate = CALCULATE(SUMX(FILTER(RELATEDTABLE(rebates),rebates[year]=2016),rebates[sales])) (not summarized)

Total2016Rebate = CALCULATE(SUM(sales[Adj 2016 Rebate])+SUM(sales[Rebate 2016B])) (summarized)

In the table below, I have a calculated column, "Adj 2016 Rebate":

Our legacy system allows for manual entry of a rebate that is stored in seperate table "rebates" for each customer by source supplier.

To calculate actual rebatable, we must add manually entered rebate value (rebates[sales])  to (sales[Rebate 2016B]), hence Total2016Rebate.

i.e. for one supplier ( Rebate (autogenerated) = $0.00 Manually entered rebate $2937.49 = Total 2016 rebate $$ = $2937.49

49

In the "summarized" view of Total2016Rebate, I get an inflated value of 205,624.30 (70 x 2,9373.49) rather than the expected 2,937.49.

When unsummarize, Total2016Rebate, I get this table, as you can see, I get a correctly calculated value, but I have the detail data burst.

Any guidance from the DAX guru's would be greatly appreciated!

[Eric_Zhang]

@fklatecki

Try

Total2016Rebate =
CALCULATE ( MAX ( sales[Adj 2016 Rebate] ) + SUM ( sales[Rebate 2016B] ) )

If it is not your case, please post some sample data. Even better you can upload the pbix file to OneDrive or any web drive and share the download link. Do note to mask sensitive data before uploading.

[fklatecki]

Thank you for the response, however using MAX did not change the results. Per your advice here is link to a shared pbix file in DropBox.

https://www.dropbox.com/sh/9zjq96vphko356t/AABLGEqzmlleozv2g6yfIS9qa?dl=0

I very much appreciate comments and suggestions from the Power BI Community.

Regards.

[Eric_Zhang]

---

@fklatecki wrote:

Thank you for the response, however using MAX did not change the results. Per your advice here is link to a shared pbix file in DropBox.

https://www.dropbox.com/sh/9zjq96vphko356t/AABLGEqzmlleozv2g6yfIS9qa?dl=0

I very much appreciate comments and suggestions from the Power BI Community.

Regards.

---

@fklatecki

Thanks for sharing the pbix file. Try

Total2016RebateM = CALCULATE(MAX(sales[Adj 2016 Rebate])+SUM(sales[Rebate 2016B])/100)