Forum Discussion

sclencioco's avatar
sclencioco
Icon for Helper I rankHelper I
9 years ago
Solved

totals per column

Hello,

 

I need to get the total percentage, but it appears that the values on total is inaccurate.

 

 

 

Here are some measurements that I created:

 

actual = COUNT('2015_POSdata'[COUNT])

 

no of stations = CALCULATE(DISTINCTCOUNT('2015_POSdata'[POS-Active Stations]),ALLEXCEPT('2015_POSdata','2015_POSdata'[MonthName]))

 

monthend day = CALCULATE(DISTINCTCOUNT('2015_POSdata'[DATE]), ALLEXCEPT('2015_POSdata','2015_POSdata'[MonthName]))

 

expected = '2015_POSdata'[no of stations] * [monthend day]

 

%age = DIVIDE([actual],[expected])

 

 

Kinfly help me please. Thank you.

 

Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    Hi sclencioco,

     

    Yes, I think you are in the right direction, you can refer to below formula to create a new table to get the summary records.

     

    Formula:

     

    Summary Table=
ADDCOLUMNS(
SUMMARIZE('2015_POSdata','2015_POSdata'[MonthName],
"actual",COUNT('2015_POSdata'[COUNT]),
"no of stations",CALCULATE(DISTINCTCOUNT('2015_POSdata'[POS-Active Stations]),ALLEXCEPT('2015_POSdata','2015_POSdata'[MonthName])),
"monthend day",CALCULATE(DISTINCTCOUNT('2015_POSdata'[DATE]), ALLEXCEPT('2015_POSdata','2015_POSdata'[MonthName]))),
"expected",[no of stations] * [monthend day],"%age",DIVIDE([actual],[expected])
)

     

     

    Regards,

    Xiaoxin Sheng

2 Replies

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

    I am considering creating a separate table just to reflect the total.

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Hi sclencioco,

     

    Yes, I think you are in the right direction, you can refer to below formula to create a new table to get the summary records.

     

    Formula:

     

    Summary Table=
ADDCOLUMNS(
SUMMARIZE('2015_POSdata','2015_POSdata'[MonthName],
"actual",COUNT('2015_POSdata'[COUNT]),
"no of stations",CALCULATE(DISTINCTCOUNT('2015_POSdata'[POS-Active Stations]),ALLEXCEPT('2015_POSdata','2015_POSdata'[MonthName])),
"monthend day",CALCULATE(DISTINCTCOUNT('2015_POSdata'[DATE]), ALLEXCEPT('2015_POSdata','2015_POSdata'[MonthName]))),
"expected",[no of stations] * [monthend day],"%age",DIVIDE([actual],[expected])
)

     

     

    Regards,

    Xiaoxin Sheng