Forum Discussion

MarcelBeug's avatar
MarcelBeug
Icon for Community Champion rankCommunity Champion
9 years ago

Second question first; I started with 6,799/10,000 and 7,971/10,000 and then started muliplying the first 2 values with a factor (trial and error). Factor 1.024 gave the outcome I was looking for.

 

First question: don't know why you wouldn't want to do that, but anyhow you can calculate the percentages as a calculated column and then include the average values in your report.

 

aslan's avatar
aslan
Icon for Helper I rankHelper I
9 years ago

I didn't understand your solution. It looks correct but i don't know how use it.

 

Calculated column may work but I get value of 67.99 and 79.71 by (MeasureA-MeasureB)/MeasureA formula. So i don't know how can make a calculated column. MeasureA =SUM(Column1NUMBER) and MeasureB = DISTINCTCOUNT(Column2STRING) ( Also both of those columns are coming from different tables)

BTW; I use  Tabular as source and work Live on PowerBI.

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    Hi aslan,

    Calculated column may work but I get value of 67.99 and 79.71 by (MeasureA-MeasureB)/MeasureA formula.

    Instead of using (MeasureA-MeasureB)/MeasureA, could you try the formula below to see if it works to get the right Grand Total you need?:smileyhappy:

    Percentage = SUMX ( 'TableName', ( [MeasureA] - [MeasureB] ) / [MeasureA] )

    Note: The 'TableName' is the name of your table whose column you're showing on the Table/Matrix with the Percentage measure.

     

    Regards

    • aslan's avatar
      aslan
      Icon for Helper I rankHelper I
      9 years ago

      Hi v-ljerr-msft

      It doesn't work. I am getting very small value in PowerBI.

  • MarcelBeug's avatar
    MarcelBeug
    Icon for Community Champion rankCommunity Champion
    9 years ago

    Let me get this straight: 73,78% is the correct answer, but you are seeking for an incorrect answer.

     

    That's why your measure results in 73.78%.

     

    In order to get your incorrect answer of 73.85%, you need a calculated column with the percentages and then take the average of those percentages (excluding zeroes),

     

    I know how to do the manipulation in Power Query to get the answer you are looking for, but not in DAX.

     

    My advice would be to accept 73.78% as the correct answer.

     

     

    • aslan's avatar
      aslan
      Icon for Helper I rankHelper I
      9 years ago

      Hi MarcelBeug 73.78% is correct but client want to see 73.85% by calculating average. But couldn't create calculated column and because of that can't calculate average.

      • MarcelBeug's avatar
        MarcelBeug
        Icon for Community Champion rankCommunity Champion
        9 years ago

        This is what I did in Power Query:

         

        let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Value1", Int64.Type}, {"Value2", Int64.Type}}),
    #"Inserted Division" = Table.AddColumn(#"Changed Type", "Inserted Division", each [Value1] / [Value2], type number),
    #"Changed Type1" = Table.TransformColumnTypes(#"Inserted Division",{{"Inserted Division", Percentage.Type}})
in
    #"Changed Type1"