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
IDAX's avatar
IDAX
Frequent Visitor
1 year ago

I'm sorry for not being precise enough. There will be more than 2 Forecast IDs. So I need to have a possibility to dynamically see what are the Actuas and two other Forecast IDs (latest one vs. previous one).

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.

     

     

    • lbendlin's avatar
      lbendlin
      Super User
      1 year ago

      Still not convinced.  I think a graphical solution is simpler.

       

       

      • IDAX's avatar
        IDAX
        Frequent Visitor
        1 year ago

        I agree 🙂

        But that is a requirement I need to fullfil. The table with dynamic comparison of selected Forecast IDs is a "must".