Forum Discussion

IDAX's avatar
IDAX
Frequent Visitor
1 year ago

dynamic diff calculation row in matrix

Dear Power BI Gurus !   I'm struggling for some time already with creating of dynamic calculation which would return difference between selected rows in matrix. Here's a sample of raw data: ...
Need Help
lbendlin's avatar
lbendlin
Super 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 or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

IDAX's avatar
IDAX
Frequent Visitor
1 year ago

here's the sample data:

Forecast IDCompanyProduct typeDateQty
ActualsComp1AJan 20248
ActualsComp1AFeb 20246
ActualsComp1BApr 20245
ActualsComp1AFeb 20242
ActualsComp2YJan 202410
ActualsComp2XJan 20244
ActualsComp2XMar 202410
ActualsComp2XJan 20245
ActualsComp3ZMay 20246
Forecast 1Comp1AJan 202410
Forecast 1Comp1BJan 202415
Forecast 1Comp2YJan 20245
Forecast 1Comp2XJan 20242
Forecast 1Comp2XFeb 20247
Forecast 1Comp2YApr 202410
Forecast 1Comp2XApr 20245
Forecast 2Comp2YMay 202410
Forecast 1Comp3ZApr 20243
Forecast 2Comp1BJan 202411
Forecast 2Comp1AFeb 20243
Forecast 2Comp1AFeb 202410
Forecast 2Comp2XJan 20246
Forecast 2Comp2XFeb 20245
Forecast 2Comp2XMar 20244
Forecast 2Comp2YJan 20244
Forecast 2Comp3ZApr 202410
Forecast 3Comp2YMay 20246
Forecast 3Comp3ZApr 20246
Forecast 3Comp1BJan 20246
Forecast 3Comp1AFeb 202410
Forecast 3Comp1AFeb 202412
Forecast 3Comp2XJan 202410
Forecast 3Comp2XFeb 20244
Forecast 3Comp2XMar 20244
Forecast 3Comp2YJan 20244
Forecast 3Comp3ZApr 20246

 

Then in the matrix I would like to see following information as in below sample:

sample 1   date hierarchy 
       
   Q1
   JanFebMarTotal
CompanyProduct typeForecast ID    
       
Comp2      
 X     
  Actuals9 1019
  Forecast 127 9
  Forecast 265415
  Forecast variance4-246
       
    date hierarchy 
sample 2      
   Q1
   JanFebMarTotal
CompanyProduct typeForecast ID    
       
Comp2      
 X     
  Actuals9 1019
  Forecast 127 9
  Forecast 3104418
  Forecast variance8-349

By using a slicer I would like to be able to pick whatever Forecast ID I want, visualize them in rows (as shown in sample) and the last row would be the dynamic one measuring a diff between those two Forecast IDs. Forecast ID called "Actuals" will be visualized in first row but only as a reference and not to be taken into any calculation.

I hope my explanation is clear enough.

Thank You for any suggestions and ideas.