cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Cross Table to Flat Table

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.

 

InkedPB_LI.jpg

 

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

 

Inkedunsummarized_LI.jpg

 

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

3 REPLIES 3
Highlighted
Microsoft
Microsoft

Re: Cross Table to Flat Table

@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.

Highlighted
Frequent Visitor

Re: Cross Table to Flat Table

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.

Highlighted
Microsoft
Microsoft

Re: Cross Table to Flat Table


@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)

 

Capture.PNG

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors