Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

PGM % help

We are converting from Tableau to Power BI. I have a PGM% calc in Tableau = sum(PGM Numerator)/sum(PGM Denominator) where the numerator is PGM and the denominator is sales that will use the filters in the report to calculate the %. I can't seem to get this to work in Power BI.

PGM % =
VAR
Net_Sales =CALCULATE([Bridges Live Scale], 'Report Line Hierarchy'[Report Line for Reporting]="Sales", 'Bridges Base Data'[Category] = "CY Forecast")
VAR
PGM =CALCULATE([Bridges Live Scale],'Report Line Hierarchy'[Report Line for Reporting]="PGM", 'Bridges Base Data'[Category] = "CY Forecast")
return
Calculate(DIVIDE(PGM,Net_Sales,0))
 
I can get the Sales and PGM values to populate in my visual using the above calculation and reflect my filters but the PGM % returns a 0% regardless. I think I am missing the SUM function from Tableau but don't know where to apply it.
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    I figured it out. No further work required!!

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Anonymous , measure [Bridges Live Scale] should be sum measure. Category and Report Line for Reporting do not have other filters on-page or visual

     

    Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

     

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hello

      Bridges Live Scale = round(IF(HASONEVALUE(Scale[Scale]),SUM('Bridges Base Data'[Amount Correct Sign])/VALUES(Scale[Scale]),SUM('Bridges Base Data'[Amount Correct Sign])),2)

       

      Scale = Thousands or millions

       

      The category can be in the filter. I was troubleshooting so I put it in the measure. The only filter we need in the calc is the reportline Sales for the one and PGM for the other. I can get those numbers but when I divide them, I get nothing.

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Here is a pic of our Bridges Base Data. We use the Report Line Code to pick the Sales and PGM.