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

The slicer will be selecting Forecast ID

lbendlin's avatar
lbendlin
Super User
1 year ago

But then you are showing all forecast IDs in the visual.  There's no need for the slicer?

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